Youtube Pivot Table in ExcelPivot tables are one of Excel's most powerful features.
A pivot table is a table of values which are aggregations of groups of individual values from a more extensive table within one or more discrete categories.
A pivot table allows you to extract the significance from a large, detailed data set.
It is an interactive way to quickly summarize large amounts of data.
Pivot: 最重要的 中心 核心
重要数据分析表 (数据透视表 樞紐表 语无伦次翻译)
汇总和分析数据的表格。
匯總其它表的數據。
可以对数据进行排序、计数、求和等操作，帮助用户快速找到数据的关键信息。
Contingency table
A contingency table is a data table that compares two variables.
In the contingency table, one data set is populated in rows, while the other data set is populated in columns.
Values of the cells where the rows and columns intersect can suggest whether or not the two sets are correlated.
(In plain words, it is just a simple table.)
透視表與列聯表 Contingency table
也稱作交叉列表 cross tabulation 交叉表 crosstab
透視表被認為更為動態，可以在其上執行某些動作；
而列聯表是靜態顯示數據。
Contingency table
列联表 可能性：
用于显示两个或多个分类变量之间关系的表格，通常用于统计分析和概率论。
Contingency
可能发生的事，不测事件；应急措施，应急储备；应急开支；可能性，意外
意外事故
同义词： possibility
=COUNTA(D1:D128)
Shortcuts to quickly lock or unlock cells in Excel
To lock a cell, simply select the cell and press the Ctrl + 1 keys.
To unlock a cell, select the cell and press the Ctrl + 2 keys.
You can also lock or unlock multiple cells at once by selecting them all and then pressing the appropriate shortcut key.
Shared Workbooks have limitations, and one in particular is the inability to edit using Excel for the web.
Therefore we highly recommend co-authoring, which is the replacement for Shared Workbooks.
Create a new workbook or open an existing workbook.
Then place it on a network location.
For example, put it on a location like \\server_name\folder_name. Don't put the file on OneDrive or SharePoint.
If you prefer those locations for the file, co-author the workbook instead.
Click Review > Share Workbook.
Note that in newer versions of Excel, the Share Workbook button has been hidden.
Here's how to unhide it.
Add Shared Workbook buttons to the Quick Access Toolbar
Click File > Options > Quick Access Toolbar.
Open the list under Choose commands from and select All Commands.
Scroll down that list until you see Share Workbook (Legacy).
Select that item and click Add.
Then scroll through the list until you see Track Changes (Legacy).
Select that item and click Add.
Scroll through the list until you see Protect Sharing (Legacy).
Select that item and click Add.
Scroll through the list until you see Compare and Merge Workbooks.
Select that item and click Add.
Click OK.
Now four new buttons will appear at the top of the Excel window.
Add Shared Workbook buttons to the Quick Access Toolbar
Select Share Workbook (Legacy) from the list, and click > to move the command to the Customize Quick Access Toolbar: list, and finally click Save.
------------
On the Editing tab, select the Allow changes by more than one user ... check box.
On the Advanced tab, select the options that you want to use for tracking and updating changes, and then click OK.
If this is a new workbook, type a name in the File name box.
Or, if this is an existing workbook, click OK to save the workbook.
If the workbook contains links to other workbooks or documents, verify the links and update any links that are broken.
Click File > Save.
When you're done, - Shared will appear at the top of the Excel window, next to the filename.
excel special keyboard shortcut key CTRL + ;
try the following methods:
a) Start Excel in safe mode (Press Win+R>Type Excel.exe /Safe), if it works well, disable and check the add-ins one by one.
b) Repair Office in Control Panel > Programs > Programs and Features
c) Use Application.OnKey Method
http://msdn.microsoft.com/en-us/library/office/ff197461(v=office.15).aspx
d) Check if the keyboard shortcut key is disabled via Group Policy
http://technet.microsoft.com/en-us/library/cc179143(v=office.15).aspx
If the issue still exists, we'd better use Process monitor to detect which process/thread block the keyboard shortcut key(CTRL + ;).
run a macro when certain cells change
the underscores _ at the end of lines of code
A space followed by an underscore tells VBA that the current statement isn't finished yet but continues on the next line – it's used to split a single line of code over two lines, in order to make the code more readable (because VBA doesn't word-wrap).
So rather than:
If Not MyRange.Paragraphs(1).Range.End = MyRange.Sections(1).Range.End Then
you could use:
If Not MyRange.Paragraphs(1).Range.End = MyRange.Sections(1).Range.End Then
But you must insert a space before the underscore.
Otherwise (in the above example) the VBA compiler would assume you thought there was such a word as “End_" and would return an error.
Filter Different Column by Multiple Criteria in Excel VBA
Filter multiple criteria in a different column(s).
Here we have a dataset where we store the names of some businessmen, which products they bought for selling purposes, the quantity of the products, and how much money they used.
Filtering Different Columns by Multiple Criteria Using VBA With Statement
Suppose you want to Filter those TVs which will cost these businessmen more than 1500 bucks. We can do this by using VBA With statement. Let's discuss the process below.
Sub MultipleCriteria()
With Range("B4:E4")
.AutoFilter Field:=2, Criteria1:="TV"
.AutoFilter Field:=3, Criteria1:=">=1500"
End With
End Sub
Filter dates between two specific dates with VBA code
Public Sub MyFilter()
Dim dateStart As Long, dateEnd As Long
dateStart = Range("E1").Value 'assume this is the start date
dateEnd = Range("E2").Value 'assume this is the end date
Range("C1:C13").AutoFilter field:=1, Criteria1:=">=" & dateStart, Operator:=xlAnd, Criteria2:="<=" & dateEnd
End Sub
Copy only visible cells
12 Methods Copy and Paste in Excel Using VBA
ActiveSheet.Range("A1:E14").SpecialCells(xlCellTypeVisible).Copy
ActiveSheet.Range("G1").PasteSpecial Paste:=xlPasteAll
Application.CutCopyMode = False
'Remove AutoFilter
ActiveSheet.AutoFilterMode = False
ActiveSheet.Range("$A$1:$J$5").AutoFilter Field:=2, Criteria1:="3"
Range("A2:A10").Select
Selection.SpecialCells(xlCellTypeVisible).Select
Selection.copy
Sheets("Email").Select
Range("A30").Select
ActiveSheet.Paste
'count the total rows in current sheet
msgbox Cells(Rows.Count, "A").End(xlUp).Row
'FilterAndCopy
Sub FilterAndCopy()
Dim wsData As Worksheet
Dim dws As Worksheet
Dim lr As Long
Dim x As Variant
Dim dict As Object
Dim it As Variant
Dim i As Long
Application.ScreenUpdating = False
Set wsData = Worksheets("Sheet1")
lr = wsData.Cells(Rows.Count, "A").End(xlUp).Row
x = wsData.Range("A2:A" & lr).Value
Set dict = CreateObject("Scripting.Dictionary")
For i = 1 To UBound(x, 1)
dict.Item(x(i, 1)) = ""
Next i
For Each it In dict.keys
On Error Resume Next
Set dws = Worksheets(CStr(it))
dws.Cells.Clear
On Error GoTo 0
If dws Is Nothing Then
Set dws = Worksheets.Add(after:=Worksheets(Worksheets.Count))
dws.Name = it
End If
With wsData.Range("A1").CurrentRegion
.AutoFilter field:=1, Criteria1:=it
wsData.Range("C2:I" & lr).SpecialCells(xlCellTypeVisible).Copy dws.Range("A1").AutoFilter
End With
Set dws = Nothing
Next it
Application.ScreenUpdating = True
End Sub
'to change the destination range dws.Range("A" & dlr)
wsData.Range("C2:I" & lr).SpecialCells(xlCellTypeVisible).Copy dws.Range("A" & dlr)
Use the AutoFilter method to perform filters on data
expression.AutoFilter(Field, Criteria1, Operator, Criteria2, VisibleDropDown)
Filter on a Single Criteria
This code would filter the data so that only cash payments were displayed.
Range("A1").CurrentRegion.AutoFilter Field:=6,Criteria1:="CASH"
Filter on Two “Or” Criteria
Use the xlOr operator to perform “Or” criteria.
Range("A1").CurrentRegion.AutoFilter Field:=6, Criteria1:="CASH", Operator:=xlOr,Criteria2:="VOUCH"
Filter on More Than Two “Or” Criteria Using Array
Range("A1").CurrentRegion.AutoFilter Field:=4, Criteria1:=Array("1", "20","30", "40"), Operator:=xlFilterValuesNB You have to use the xlFilterValues operator when using an Array as your criteria.
Filter on Two “And” Criteria
Use the xlAnd operator to perform “And” criteria.
Range("A1").CurrentRegion.AutoFilter Field:=5,Criteria1:=">=5000", Operator:=xlAnd,Criteria2:="<=6000"
Filter on Top/Bottom X Values
Use the Criteria parameter to specify the number of records to return.
Range("A1").CurrentRegion.AutoFilter Field:=5, Criteria1:=25, Operator:=xlTop10Items
Perform Dynamic Date Filters
Excel's Autofilter allows you to apply date filters that for example filter for dates in the current month, quarter or year, as well as filters for past and future periods. These can be accessed in VBA. You will need to use xlFilterDynamic as your Operator. The following code filters the date field for dates in the current month. Use CTRL SPACE to open the IntelliSense list which includes all the dynamic filter names.
Range("A1").CurrentRegion.AutoFilter field:=2, Criteria1:=xlFilterAllDatesInPeriodMonth, Operator:=xlFilterDynamic
Perform Dynamic Average Filters
This code filters the TRANS_VALUE column for the above average values.
Range("A1").CurrentRegion.AutoFilter Field:=5,Criteria1:=xlFilterAboveAverage, Operator:=xlFilterDynamic
This code filters for cells containing a red traffic light.
Range("A1").CurrentRegion.AutoFilter Field:=1, Criteria1:=ActiveWorkbook.IconSets(xl3TrafficLights1).Item(1), Operator:=xlFilterIcon
Perform Wildcard Filters on Text Fields
You can use the * and ? wildcard characters in the usual way.
Range("A1").CurrentRegion.AutoFilter field:=3,Criteria1:="ADC-AA?*"
To apply filters to more than one field, you could do this…
WithRange("A1").CurrentRegion
.AutoFilter Field:=6,Criteria1:="CASH", Operator:=xlOr, Criteria2:="VOUCH"
.AutoFilter Field:=4, Criteria1:=Array("1", "20","30", "40"), Operator:=xlFilterValues
End With
Excel VBA to Filter by Multiple Criteria
Remove Specific Values with VBA to Filter in Same Column by Multiple Criteria in Excel
We will remove the value California & Texas from the column City with an excel VBA filter.
Sub Remove()
Application.DisplayAlerts = True
Worksheets("Remove").Activate
Columns("C:C").Select
Selection.AutoFilter
ActiveSheet.Range("$C$4:$C$15").AutoFilter Field:=1, Criteria1:="<>California", Criteria2:="<>Texas", Operator:=xlAnd
End Sub
Finally, we get results like the image below.
We can see that the values California & Texas have been filtered from the column City.
Keep Particular Values in Same Column with Excel VBA
Unlike the previous example in this example, we will keep a particular value in the same column instead of removing it.
To do this we will apply an excel VBA filter by multiple criteria in the same column.
In the previous example, we have removed the value ‘California' & ‘Texas'.
But, in this example, we will keep these values after applying filters whereas we will remove the other ones.
Let's see the steps to follow for this example.
Sub Keep()
Application.DisplayAlerts = True
Worksheets("KEEP").Activate
Columns("C:C").Select
Selection.AutoFilter
ActiveSheet.Range("$C$4:$C$15").AutoFilter Field:=1, Criteria1:=Array( "California", "Texas"), Operator:=xlFilterValues
End Sub
As a result, we can see the result in the below image.
Only the values present in the City column are California and Texas.
Other values have been filtered.
VBA to Filter by Multiple Criteria with Advanced Criteria Range in Same Column
In the third example, we will apply excel VBA to filter by multiple criteria with advanced criteria ranges in the same column.
We will use the dataset of our previous examples.
From the image below we can see the advanced criteria also.
We are going to filter the data set for two criteria.
The first one is that the city will be New York and the sales amount will be greater than $3000.
The second one is that the city will be in California and the sales amount will be less than $3000.
Just follow the below steps to perform this method.
Sub Advanced_Criteria()
Range("B4:D15").AdvancedFilter Action:=xlFilterInPlace, CriteriaRange:=Range("F6:G8")
End Sub
Lastly, we get results like the image below.
The filtered data in the image below follows our criteria range.
Filter in Same Column Using VBA with OR Criteria in Excel
Another example to apply filters in the same column is to use VBA with ORcriteria in excel.
We will use two criteria in our dataset.
The OR criteria will return a value if the values from our dataset meet any of the defined two criteria.
To perform this example in our dataset follow the below steps.
Sub OR_Criteria()
Worksheets("OR").Range("B4").AutoFilter Field:=2, Criteria1:="New York", Operator:=xlOr, Criteria2:=">3000"
End Sub
Lastly, we get our dataset filtered for the defined criteria range.
Insert AND Criteria with Excel VBA to Filter in Same Column
The ANDcriteria will return the values which will meet both criteria that we will fix.
Using AND criteria we can filter in the same column using excel VBA by multiple criteria.
We will filter the data in our dataset for sales values between $2000 & $3500.
Let's take a look at the steps to apply AND criteria with the VBA filter in our dataset.
Sub AND_Criteria()
Worksheets("AND").Range("B4").AutoFilter Field:=3, Criteria1:=">2000", Operator:=xlAnd, Criteria2:="<3500"
End Sub
Lastly, we can see the result for filtered data in the image below.
Put VBA to Filter by Multiple Criteria in Same Column with Data Range
In the last example, we will apply VBA to filter with data range.
To do this we will fix multiple criteria in the same column.
In spite of the use of AND criteria, we are showing this example since we will filter the dataset for a date range.
Follow the below steps to apply a VBA filter with the data range in our dataset.
Sub Date_Range()
Worksheets("DateRange").Range("B4:D15").AutoFilter Field:=1, Criteria1:=">=12-03-21", Operator:=xlAnd, Criteria2:="<=12-08-21"
End Sub
In the end, we get the Date column filtered by our given criteria.
Excel VBA to Turn off AutoFilter
In the following image, we can see that the filter is already applied to the dataset.
In this section, we will use VBA to turn off the filter applied in this dataset.
So, let's see the steps to perform this method.
Sub Turn_Off_Filter()
Worksheets("TurnOff").AutoFilterMode = False
End Sub
Turn on AutoFilter in Excel Using VBA
Sub Turn_On_Filter()
If Not Worksheets("TurnOn").Range("B4").AutoFilter Then
Worksheets("TurnOn").Range("B4").AutoFilter
End If
End Sub
As a result, we can see filter icons in the header cells of our dataset.
Check If Filter Is Applied or Not
Suppose we are working with thousands of cells.
It's not possible to identify if the dataset has filters applied to it only by looking at it.
So, in this method, we will use a VBA code to see if the dataset contains a filter or not.
Let's see the steps which we have to follow in this method.
Sub Filter_Check()
If ActiveSheet.AutoFilterMode = True Then
MsgBox "Active worksheet have filters already in place"
Else
MsgBox "Active worksheet doesn't contain any filter
End If
End Sub
Lastly, we get a message box displaying the message ‘Active worksheet have filters already in place'.
The Find method can be written as follows:
Find(What, After, LookIn, LookAt, SearchOrder, SearchDirection, MatchCase, MatchByte, SearchFormat)
That looks complicated!
Let's break it down into smaller chunks:
1. What (required): The only required parameter, What tells the Excel what to actually look for.
This can be anything – string, integer, etc.).
Syntax: expression.Find(What:="x")
2. After (optional): This specifies the cell after which the search is to begin.
This must always be a single cell; you can't use a range here.
If the after parameter isn't specified, the search begins from the top-left corner of the cell range.
Syntax: expression.Find(What:="x", After:=ActiveCell)
Here, we've used ‘ActiveCell' as our starting cell, though you can also specify a particular cell.
3. LookIn (optional): This tells Excel what type of data to look in, such as xlFormulas.Syntax:expression.Find(What:="x", After:=ActiveCell, LookIn:=xlFormulas)
4. LookAt (optional): This tells Excel whether to look at the whole set of data, or only a selected part.
It can take two values: xlWhole and xlPartSyntax: expression.Find(What:="x", After:=ActiveCell, LookIn:=xlFormulas, LookAt:=xlPart)
5. SearchOrder(optional): You have the choice of telling Excel whether to search by rows or by columns, i.e. xlByRows or xlByColumnsSyntax:expression.Find(What:="x", After:=ActiveCell, LookIn:=xlFormulas, LookAt:=xlPart, SearchOrder:=xlByRows)
6. SearchDirection(optional): This is used to specify whether Excel should search for the next or the previous matching value.
You can use either xlNext (to search for next matches) or xlPrevious (to search for previous matches).
Syntax:expression.Find(What:="x", After:=ActiveCell, LookIn:xlFormulas, LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext)
7. MatchCase(optional): Self-explanatory; this tells Excel whether it should match case when doing the search or not.
The default value is False.Syntax:expression.Find(What:="x", After:=ActiveCell, LookIn:xlFormulas, LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:=True)
8. MatchByte(optional): This is used if you have installed double-type character set (DBCS).
Understanding DBCS is beyond the scope of this tutorial.
Like MatchCase, this can also have two values: True or False, with default being False.
Syntax:expression.Find(What:="x", After:=ActiveCell, LookIn:xlFormulas, LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:=True, MarchByte:=False)
The MatchByte parameter is usually not a part of the Find range if you record a macro using Excel's built-in Find & Replace function (CTRL + F).
9. SearchFormat(optional): This parameter is used when you want to select cells with a specified property.
It is used in conjunction with the FindFormat property.
Say, you have a list of cells where one particular cell (or cell range) is in Italics.
You could use the FindFormat property and set it to Italics.
If you later use the SearchFormat parameter in Find, it will select the Italicized cell.
SearchFormat can have two values: True and False.
Default is false.
Syntax:expression.Find(What:="x", After:=ActiveCell, LookIn:xlFormulas, LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:=True, MarchByte:=False, SearchFormat:=False)
Find Method Example:
Let's say we have a spreadsheet where the first column is filled with an arithmetic progression:
1, 4, 7, 10, 13, 16, 19….
The entire column from A1 to A65000 is filled.
We want to find a specific value, say, 24652, in this progression.
To do this, we can enter the following formula:
Cells.Find(What:="24652", After:=ActiveCell, LookIn:=xlFormulas, LookAt:= xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:=False , SearchFormat:=False).Activate
Which immediately finds us our required cell – A8218 upon running the macro:
We entered all the parameters above for illustrative purposes.
In your actual formula, you can use just the value to be searched (“What:=").
Instead of using ‘Cells.' At the beginning, you can also specify a particular range.
To see how different search options affect the Find method syntax, try recording macros with Excel's built-in Find function (CTRL + F).
You can also use .FindNext and .FindPrevious to search for next/pervious matching values.
Keep in mind that when you use the Find method once, Excel stores all the parameters you entered (‘SearchFormat', ‘MatchCase', etc.).
Thus, if you set ‘MatchCase' to true once, it remains true for subsequent searches as well until you explicitly change it to false.
Want to use macros but hate programming? Try this non-coding approach to Excel VBA and macros.
Applications
You'll use the Find method a lot to find and/or replace values data in your VBA programs.
Its primary applications are:
Search for and replace values in Cell Value
Search for values in Cell Formula
Most importantly, the Find method acts as a far more efficient alternative to using loops to look for data.
The performance boost is very significant – a search like the kind outlined above in our example using loops would take anywhere from 20-120 milliseconds.
The same with Find takes less than 5 milliseconds.
If you're in the habit of using loops, you'll find a worthy (and easier to use) ally in Find.
Find Value
excel.xllookatVBA Coding Basics
sht.Cells.Replace What:=fndList(x), Replacement:=rplcList(x), LookAt:=xlwhole, SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, ReplaceFormat:=False
LookAt:=xlwhole, xlPart
Sub FindValue()
Dim c As Range
Dim firstAddress As String
With Worksheets(1).Range("A1:A500")
Set c = .Find(2, lookin:=xlValues)
If Not c Is Nothing Then
firstAddress = c.Address
Do
c.Value = 5
Set c = .FindNext(c)
Loop While Not c Is Nothing
End If
End With
End Sub
Sub FindString()
Dim c As Range
Dim firstAddress As String
With Worksheets(1).Range("A1:A500")
Set c = .Find("abc", LookIn:=xlValues)
If Not c Is Nothing Then
firstAddress = c.Address
Do
c.Value = Replace(c.Value, "abc", "xyz")
Set c = .FindNext(c)
Loop While Not c Is Nothing
End If
End With
End Sub
Sub UseLookAt()
Dim cell As Range
Set cell = Range("e1:e7").Find("床", Lookat:=xlPart)
Debug.Print cell.Address
Set cell = Range("e1:e7").Find("床", Lookat:=xlWhole)
Debug.Print cell.Address
End Sub
Sub FindAll()
'PURPOSE: Find all cells containing a specified values
Dim fnd As String, FirstFound As String
Dim FoundCell As Range, rng As Range
Dim myRange As Range, LastCell As Range
'value to find (must be in string form)?
fnd = "12"
Set myRange = ActiveSheet.UsedRange
Set LastCell = myRange.Cells(myRange.Cells.Count)
Set FoundCell = myRange.Find(what:=fnd, after:=LastCell)
'Test to see if anything was found
If Not FoundCell Is Nothing Then
FirstFound = FoundCell.Address
Else
GoTo NothingFound
End If
Set rng = FoundCell
'Loop until cycled through all unique finds
Do Until FoundCell Is Nothing
'Find next cell with fnd value
Set FoundCell = myRange.FindNext(after:=FoundCell)
'Add found cell to rng range variable
Set rng = Union(rng, FoundCell)
'Test to see if cycled through to first found cell
If FoundCell.Address = FirstFound Then Exit Do
Loop
'Select Cells Containing Find Value
rng.Select
Exit Sub
'Error Handler
NothingFound:
MsgBox "No values were found in this worksheet"
End Sub
We can take the above code a step further and actually take an action with the Find Range.
With the below VBA macro, you can highlight all cells containing the value 12 with a yellow cell fill.
Sub HighlightFindValues()
'PURPOSE: Highlight all cells containing a specified values
Dim fnd As String, FirstFound As String
Dim FoundCell As Range, rng As Range
Dim myRange As Range, LastCell As Range
'value to find (must be in string form)?
fnd = "Kentucky"
Set myRange = ActiveSheet.UsedRange
Set LastCell = myRange.Cells(myRange.Cells.Count)
Set FoundCell = myRange.Find(what:=fnd, after:=LastCell)
'Test to see if anything was found
If Not FoundCell Is Nothing Then
FirstFound = FoundCell.Address
Else
GoTo NothingFound
End If
Set rng = FoundCell
'Loop until cycled through all unique finds
Do Until FoundCell Is Nothing
'Find next cell with fnd value
Set FoundCell = myRange.FindNext(after:=FoundCell)
'Add found cell to rng range variable
Set rng = Union(rng, FoundCell)
'Test to see if cycled through to first found cell
If FoundCell.Address = FirstFound Then Exit Do
Loop
'Highlight Found cells yellow
rng.Interior.Color = RGB(255, 255, 0)
Exit Sub
'Error Handler
NothingFound:
MsgBox "No values were found in this worksheet"
End Sub
Scrape Data
Guide to Extracting Website Data Using Excel VBA Scrape Data from Website to Excel Excel VBA Web Scraping with Chrome
Sub scrape_quotes()
Dim browser As InternetExplorer
Dim page As HTMLDocument
Set browser = New InternetExplorer
browser.Visible = True
browser.navigate ("https://quotes.toscrape.com")
End Sub
Sub scraping_web()
Dim chrome As Selenium.ChromeDriver
Dim row_no, col_no As Integer
row_no = 2
Application.ScreenUpdating = False
Set chrome = New Selenium.ChromeDriver
chrome.Start
chrome.Get "https://www.exceldemy.com/excel-vba-translate-formula-language/"
For Each tr In chrome.FindElementByClass("wpsm-table"). _
FindElementByTag("tbody").FindElementsByTag("tr")
col_no = 2
For Each td In tr.FindElementsByTag("td")
Sheet1.Cells(row_no, col_no).Value = td.Text
col_no = col_no + 1
Next td
row_no = row_no + 1
Next tr
Application.Wait Now + TimeValue("00:00:20")
End Sub
VBA Advanced Filter is one of the many hidden gems that Excel VBA offers to make our time more productive.
VBA Advanced Filter requires very little code, is one of the fastest ways to copy data, and provides advanced filtering options that we cannot get anywhere else.
VBA Advanced Filter Quick Guide
Using the criteria with AdvancedFilter is very powerful.
You can see the possible options in the table below:
Task
Cell formula
Examples where true
Contains
="=*Pea*"
Peach, Pea, Appear
Does not contain
="<>*Pea*"
any text that does not contain Pea
Exact match
="=Pea"
Pea
Does not exactly match
="<>Pea"
Peach, Pear etc.
Starts with
="=Pea*"
Peach, Pear, Pea
Ends with
="=*Pea"
SweetPea, GreenPea
Use the ? symbol to represent any single character
="=Pea?"
Pear, Peas or any 4 letter word starting with "Pea"
Important Note: A Criteria column header must exist as a List range column header.
For example, if the Criteria column header is "Fruit" then there must be a List range column header called "Fruit".
Here are some important things to know about the Criteria column headers:
You don't need to include a column header in the criteria if you are not filtering by this column.
What is Advanced Filter
Advanced Filter is a tool that is available in the Sort & Filter section of the Data tab on the Excel Ribbon:
It allows us to filter data based on a Criteria range.
This allows us to do more advanced filtering than the standard filtering on the worksheet.
A second advantage of using Advanced Filter is that we have the option to copy the results to a new range if we choose.
Using Advanced Filter is quite simple as you can see from the dialog:
We filter in-place or we copy to another location.
We then simply need the data range(List range) and the Criteria Range.
If we decided to copy to another location then we provide the "Copy to" range.
Using Advanced Filter is very useful in VBA because it is extremely fast, powerful and as we will see it requires very little code.
VBA Advanced Filter on YouTube
To see me working with Advanced Filter, check out this YouTube video:
VBA Advanced Filter Parameters
The following table shows the parameters of the AdvancedFilter function:
Parameter
Optional
Type
Details
Action
Required
xlFilterAction
xlFilterInPlace or xlFilterCopy.
CriteriaRange
Optional
Range
Range of the criteria used for filtering the data.
CopyToRange
Optional
Range
Destination range if Action is set to xlFilterCopy.
Unique
Optional
Boolean
True for unique records only.
You can read about the parameters on the Microsoft help page.
AdvancedFilter requires three ranges to run(or two if you are using xlFilterInPlace as the Action parameter):
List range - data to filter.
Criteria range - how to filter.
Copy To range - where to place the results if the Action parameter is set to xlFilterCopy is set.
AdvancedFilter is a range Function.
This means you get the range of data you wish to filter and then call the AdvancedFilter function of that range:
DataRange.AdvancedFilter Filter Action, Criteria, [CopyTo], [Unique]
We can filter in place or we can copy the filter results to another location.
This means there are two ways to use AdvancedFilter:
' Filter in place
rgData.AdvancedFilter xlFilterInPlace, rgCriteriaRange
' Filter and copy data
rgData.AdvancedFilter xlFilterCopy, rgCriteriaRange, rgDestination
The first parameter indicates the way to apply the filter:
xlFilterInPlace - Filter the original data.
xlFilterCopy - Copy the filter results to a new range.
If we use xlFilterInPlace then we don't need the destination range.
To remove duplicate records we simply set the Unique parameter to True.
Otherwise, duplicate records are ignored:
' Filter in place
rgData.AdvancedFilter xlFilterInPlace, rgCriteriaRange, , True
' Filter and copy data
rgData.AdvancedFilter xlFilterCopy, rgCriteriaRange, rgDestination, True
Understanding the Advanced Filter Ranges
The following screenshot shows an example of the 3 ranges.
The List(or data) range is shaded blue, the Criteria range is green, and the CopyTo range is yellow:
The following subsection provides a quick guide to each of the ranges:
Criteria Range
The criteria headers must be one of the List Range column headers.
If not then it will be ignored.
Criteria headers can be in any order.
You can include as many or as few Criteria headers as you need.
You can use the same header multiple times - this allows us to do multiple AND operations on the same column.
CopyTo Range
This range is only used when the Action parameter is set to xlFilterCopy.
To avoid errors this range should be the Header row of the output destination.
You can use any(or all) columns from the List range as your output and they can be in any order.
The columns headers in this range must be a List Range column header or you will get a VBA Runtime Error 1004.
List Range
The List Range is the range of data that will be filtered.
You must include the headers as part of the List Range.
If you set the Action parameter to xlFilterInPlace then the List data will be filtered.
If you set the Action parameter to xlFilterCopy then the results will be copied to the location which is specified in the CopyToRange parameter.
Writing the VBA Code
The easiest way to define the data range is to use CurrentRegion although you can get the range any way you like.
Using CurrentRegion gets all the adjacent data to the specified cell or range.
We can use CurrentRegion like this:
Dim rgData As Range, rgCriteriaRange As Range
Set rgData = Range("A4").CurrentRegion
Set rgCriteriaRange = Range("A1").CurrentRegion
To set the CopyTo range, you specify the entire heading row.
You can use a simple trick with CurrentRegion to get the CopyToRange header row.
First, use CurrentRegion and then take the first row of the resulting range:
Dim rgCopyToRange As Range
Set rgCopyToRange = shFruit.Range("E4").CurrentRegion.Rows(1)
The full VBA Advanced filter code looks like this:
Sub RunAdvancedFilter()
' Declare the variables
Dim rgData As Range, rgCriteriaRange As Range, rgCopyToRange As Range
' Set the ranges
Set rgData = Sheet1.Range("A4").CurrentRegion
Set rgCriteriaRange = Sheet1.Range("A1").CurrentRegion
Set rgCopyToRange = Sheet1.Range("D1").CurrentRegion.Rows(1)
' Run AdvancedFilter
rgData.AdvancedFilter xlFilterCopy, rgCriteriaRange, rgCopyToRange
End Sub
You can run this code for pretty much any AdvancedFilter that you want to do.
All you need to do is to change the ranges as appropriate.
You can change Sheet1 in the code to any worksheet variable or code name.Important Note: When we run AdvancedFilter using VBA, the ranges do not need to be on the same worksheet or even in the same workbook.
VBA Advanced Filter Clear
If we Filter the data in place then we can use ShowAllData to remove the filter.
We should check the filter is turned on first so we don't get an error.
We can use the following code to check and clear the filter if it exists:
If Sheet1.FilterMode = True Then
Sheet1.ShowAllData
End If
If we are we filter using copy then Advanced Filter will automatically remove existing data from the destination before copying.
However, if you want to simply clear the data you can do it like this:
Sheet1.Range("E7").CurrentRegion.Offset(1).ClearContents
This highlights all the adjacent data to the output headers.
It moves down one row using Offset to avoid clearing the header row.
VBA Advanced Filter Criteria
Check out this YouTube video to see me using Advanced Filter Criteria:
To use Criteria on a filter we use the columns headers of the List range with the criteria below them.
The following criteria will return all rows where the Fruit column contains the text Orange:
This criteria will return the following rows:
Advanced Filter Multiple Criteria
We can use the columns in any row to filter by multiple criteria.
This allows us to filter using AND logic e.g.
If Fruit equals "Apple" AND City equals "New York":
We can use multiple rows if we want to filter using OR logic e.g.
If Fruit equals "Apple" OR Fruit equals "Pear" OR Fruit equals "Plum":
Let's have a look at examples of using Multiple Criteria with Advanced Filter:
Advanced Filter Multiple Criteria Examples
In our first example we will start with a simple AND filter:
These criteria return all the rows that have the fruit Orange AND the city Berlin:
In our next example, we are looking for a city that starts with S AND has sales of less than 500:
These criteria will return the following rows:
We can use any column header multiple times in the criteria range.
For example, we can use the Sales column twice to get a number between 300 and 500:
These criteria will return the following rows:
Advanced Filter Criteria - OR
We use columns in a row when we want to do an AND operation.
If we want want to do an OR operation we use rows in the Criteria filter.
In the following example we want to return rows where the Fruit is either a Peach OR a Banana:
This will return the following rows:
In the following example, we want to return any rows that have a fruit Banana OR sales that are greater than 900:
These criteria will return the following rows:
Combining AND Criteria and OR Criteria
Let's look at an example of combining AND criteria with OR criteria.
This criteria filters by rows where (Fruit is Lemon AND City is Singapore) OR (Fruit is Orange AND City is Paris):
These are the results:
Using Formulas as Criteria
While the standard criteria methods offer powerful filtering methods they have limitations.
The beauty of the AdvancedFilter is that we can use worksheet formulas in the criteria.
There are 3 rules when using formulas in the criteria range:
No heading.
The formula must result in True or False.
The formula should reference the first row of the data range.
Imagine we have the following data:
We want to filter by games where the total number of goals scored was 2. We cannot do this using the normal criteria so we create a formula:
=B5+D5=2
We place this formula in cell A2:
You can see that we have followed the rules above:
We have no header in the criteria.
The result of the formula is False.
The formula refers to the first row of the data i.e. B5 and D5.
The rows we get back are:
Note: If we want to use a formula on another row in the Criteria we should still refer to the first row of the data e.g.:
Cell A2 formula: =B5+D5=2
Cell A3 formula: =B5+D5=5
Cell A3 formula: =B5+D5=7
Advanced Filter Case Sensitive Criteria
There isn't a simple way to use case sensitivity in our Criteria therefore we use a formula instead:
=EXACT(A5, "Pea")
Make sure to follow the Formula rules in the previous section to ensure this works correctly.
VBA Advanced Filter Dates
Take a look at the following formula for the date criteria:
="<1/9/2021″
This formula will work fine when we run Advanced Filter from the ribbon.
But if we run Advanced Filter using VBA it will not return any records.
Instead, we have to use a formula like this:
="<" & DATE(2021,9,1)
When we use this in the criteria like this:
we get:
If you want to do between dates then you can use the formulas in two columns.
For example, imagine we want to get all the records in August 2021, then we can use the following formulas:
A2: ="<=" & DATE(2021,8,31)
B2: =">=" & DATE(2021,8,1)
This will return the following rows:
VBA Advanced Filter Advantages and Limitations
Advanced Filter is easy to use and does its job very well.
However, like every tool it has advantages and limitations to what it can do.
The following are the advantages of using the Advanced Filter:
Speed - It is the fastest VBA method for copying and filtering data although multiple calls will slow it down.
Advanced filtering - provides in-depth filtering options including the use of formulas.
Requires very little code - You can use the same code most of the time and it's simplistic compared to other methods of copying and filtering code in VBA.
Formatting - When copying the results it automatically formats the result data to match the original data.
The following are the limitations of the Advanced Filter which you should be aware of:
Speed - using AdvancedFilter is extremely fast but calling it multiple times in the same code, will cause it to run slower.
Criteria can only use ranges - you cannot use an array for the Criteria.
The workaround is to write the array to a range and then use it as the Criteria.
Cannot alter data - AdvancedFilter simply filters and copies the data.
You cannot make changes to the data after filtering and before copying.
Cannot append data - To append data you need to write extra code.
Error 1004 - Extract Range
The most common error with the advanced filter is: Error 1004 - the extract range has a missing or invalid field name.VBA Runtime Error 1004 occurs when one or more of the output column headers do not exist in the original data as we can see in this example:
If this error occurs you should ensure that the CopyTo range has the correct column headers with the correct spelling and that it is referencing the correct range.
What to do if Advanced Filter is not working
In general, when Advanced Filter throws an error, the problem is in one of the ranges.
If you have an error the following checks should fix most if not all errors:
Ensure all the range variables are referencing the expected ranges.
Ensure the Criteria and CopyTo column headers are correctly spelled and exist in the column headers of the List range.
Ensure that the CopyTo range references the header row only.
Ensure that any Criteria columns using formulas do not have a header.
Make sure that there are no trailing spaces in Criteria.
TIP: If you are debugging the code you can check any range using the Address property of the range:
For i = LBound(arr) To UBound(arr)
Next i
Or
For i = LBound(arr,1) To UBound(arr,1)
Next i
For i = LBound(arr) To UBound(arr)
Next i
Or
For i = LBound(arr,1) To UBound(arr,1)
Next i
Read all items(2D)
For i = LBound(arr,1) To UBound(arr,1)
For j = LBound(arr,2) To UBound(arr,2)
Next j
Next i
For i = LBound(arr,1) To UBound(arr,1)
For j = LBound(arr,2) To UBound(arr,2)
Next j
Next i
Read all items
Dim item As Variant
For Each item In arr
Next item
Dim item As Variant
For Each item In arr
Next item
Pass to Sub
Sub MySub(ByRef arr() As String)
Sub MySub(ByRef arr() As String)
Return from Function
Function GetArray() As Long()
Dim arr(0 To 5) As Long
GetArray = arr
End Function
Function GetArray() As Long()
Dim arr() As Long
GetArray = arr
End Function
Receive from Function
Dynamic only
Dim arr() As Long
Arr = GetArray()
Erase array
Erase arr
*Resets all values to default
Erase arr
*Deletes array
String to array
Dynamic only
Dim arr As Variant
arr = Split("James:Earl:Jones",":")
Array to string
Dim sName As String
sName = Join(arr, ":")
Dim sName As String
sName = Join(arr, ":")
Fill with values
Dynamic only
Dim arr As Variant
arr = Array("John", "Hazel", "Fred")
Range to Array
Dynamic only
Dim arr As Variant
arr = Range("A1:D2")
Array to Range
Same as dynamic
Dim arr As Variant
Range("A5:D6") = arr
search in excel
Sub filterCriteria()
ActiveSheet.Range("B3:D3").AutoFilter Field:=2, Operator:=xlFilterValues, Criteria1:=Array("Emily", "Daniel", "Gabriel")
End Sub
Sub filterCriteria3()
Dim ID_range, k As Variant
ID_range = Application.Transpose(ActiveSheet.Range("F4:F6"))
For k = LBound(ID_range) To UBound(ID_range)
ID_range(k) = CStr(ID_range(k))
Next k
ActiveSheet.Range("B3:D3").AutoFilter Field:=1, Operator:=xlFilterValues, Criteria1:=ID_range
End Sub
Sub MultipleCriteriaAndFilter()
Worksheets("xland_filter").Range("B4:E13").AutoFilter Field:=4, Criteria1:=">2", Operator:=xlAnd, Criteria2:="<=5"
End Sub
Sub MultipleCriteriaOrFilter()
Worksheets("xlor_filter").Range("B4").AutoFilter Field:=3, Criteria1:="<1600", Operator:=xlOr, Criteria2:=">=2100"
End Sub
This example finds all cells in the range A1:A500 in worksheet one that contain the value 2, and changes the entire cell value to 5. That is, the values 1234 and 99299 both contain 2 and both cell values will become 5.
Sub FindValue()
Dim c As Range
Dim firstAddress As String
With Worksheets(1).Range("A1:A500")
Set c = .Find(2, lookin:=xlValues)
If Not c Is Nothing Then
firstAddress = c.Address
Do
c.Value = 5
Set c = .FindNext(c)
Loop While Not c Is Nothing
End If
End With
End Sub
This example finds all cells in the range A1:A500 on worksheet one that contain the substring "abc" and then replaces "abc" with "xyz".
Sub FindString()
Dim c As Range
Dim firstAddress As String
With Worksheets(1).Range("A1:A500")
Set c = .Find("abc", LookIn:=xlValues)
If Not c Is Nothing Then
firstAddress = c.Address
Do
c.Value = Replace(c.Value, "abc", "xyz")
Set c = .FindNext(c)
Loop While Not c Is Nothing
End If
End With
End Sub
Sub Find_from_Array()
Dim Rng As Range
Dim Author() As Variant
Set Rng = Range("C5:C14")
Author = Array("George R.R.Martin", "Siddhartha Mukherjee")
For i = 0 To UBound(Author)
For j = 1 To Rng.Rows.Count
If Rng.Cells(j, 1) = Author(i) Then
Range(Rng.Cells(j, 1).Offset(0, -1), Rng.Cells(j, 1).Offset(0, 2)).Interior.Color = vbGreen
End If
Next j
Next i
End Sub
text_string = "a sadf dff assd fggd dd"
myArray = Split(text_string) ' split by space
MsgBox UBound(myArray) ' 5
MsgBox LBound(myArray) ' 0
The find method doesn't support multiple criteria in its search.
You could .Find one criteria in a loop and test the other three criteria for each match found until all four match.
Code:
Dim Found As Range, Firstfound As String
Dim rngSearch As Range
Dim Criteria As Variant
Set rngSearch = Sheets("DataSheet").Range("B:B")
Criteria = Sheets("ControlSheet").Range("I12:I15").Value
Set Found = rngSearch.Find(What:=Criteria(1, 1), LookIn:=xlValues, LookAt:=xlWhole, SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:=False)
If Not Found Is Nothing Then
Firstfound = Found.Address
Do
If Found.EntireRow.Range("F1").Value = Criteria(2, 1) And Found.EntireRow.Range("I1").Value = Criteria(3, 1) And Found.EntireRow.Range("J1").Value = Criteria(4, 1) Then Exit Do 'Match found
Set Found = rngSearch.FindNext(After:=Found)
If Found.Address = Firstfound Then Set Found = Nothing
Loop Until Found Is Nothing
End If
If Not Found Is Nothing Then
Application.Goto Found.EntireRow
Else
MsgBox "Nothing matched all four criteria. ", , "No Match Found"
End If
Speed up data entry and validation with AutoComplete
You can't have autocomplete without VBA
But there is a small workaround without using VBA
Enter some "dummy" single letter entries in your source list, A, B, C etc.
and then sort the entire list.
Create the DV dropdown with that sorted source list.
In the DV cell, enter T then press the dropdown, and you will be at the
beginning of your T entries - saves a lot of scrolling.
See http://www.contextures.com/xlDataVal02.html and/or http://www.contextures.com/xlDataVal10.html
AutoComplete for dropdown lists is now available in Excel
run a macro when certain cells change in Excel
Private Sub Worksheet_Change(ByVal Target As Range)
Dim KeyCells As Range
' The variable KeyCells contains the cells that will
' cause an alert when they are changed.
Set KeyCells = Range("A1:C10")
If Not Application.Intersect(KeyCells, Range(Target.Address)) Is Nothing Then
' Display a message when one of the designated cells has been changed.
MsgBox "Cell " & Target.Address & " has changed."
End If
End Sub
select cells/ranges by using Visual Basic in Excel
⇧
https://williamkpchan.github.io/start20120304.htm
isual Basic methods listed in the following table.
Method Arguments
------------------------------------------
Activate none
Cells rowIndex, columnIndex
Application.Goto reference, scroll
Offset rowOffset, columnOffset
Range cell1
cell1, cell2
Resize rowSize, columnSize
Select none
Sheets index (or sheetName)
Workbooks index (or bookName)
End direction
CurrentRegion none
The examples in this article use the properties in the following table.
Property Use
---------------------------------------------------------------------
ActiveSheet to specify the active sheet
ActiveWorkbook to specify the active workbook
Columns.Count to count the number of columns in the specified item
Rows.Count to count the number of rows in the specified item
Selection to refer to the currently selected range
Select a Cell on the Active Worksheet
⇧
To select cell D5 on the active worksheet, you can use either of the following examples:
ActiveSheet.Cells(5, 4).Select
-or-
ActiveSheet.Range("D5").Select
Select a Cell on Another Worksheet in the Same Workbook
⇧
To select cell E6 on another worksheet in the same workbook, you can use either of the following examples:
Application.Goto ActiveWorkbook.Sheets("Sheet2").Cells(6, 5)
-or-
Application.Goto (ActiveWorkbook.Sheets("Sheet2").Range("E6"))
Or, you can activate the worksheet, and then use method 1 above to select the cell:
Sheets("Sheet2").Activate
ActiveSheet.Cells(6, 5).Select
Select a Cell on a Worksheet in a Different Workbook
⇧
To select cell F7 on a worksheet in a different workbook, you can use either of the following examples:
Application.Goto Workbooks("BOOK2.XLS").Sheets("Sheet1").Cells(7, 6)
-or-
Application.Goto Workbooks("BOOK2.XLS").Sheets("Sheet1").Range("F7")
Or, you can activate the worksheet, and then use method 1 above to select the cell:
Workbooks("BOOK2.XLS").Sheets("Sheet1").Activate
ActiveSheet.Cells(7, 6).Select
Select a Range of Cells on the Active Worksheet
⇧
To select the range C2:D10 on the active worksheet, you can use any of the following examples:
ActiveSheet.Range(Cells(2, 3), Cells(10, 4)).Select
ActiveSheet.Range("C2:D10").Select
ActiveSheet.Range("C2", "D10").Select
or
ActiveSheet.Range(ActiveSheet.Cells(2, 3), ActiveSheet.Cells(10, 4)).Select
or, alternatively, it could be simplified to this:
Range(Cells(2, 3), Cells(10, 4)).Select
Select a Range of Cells on Another Worksheet in the Same Workbook
⇧
To select the range D3:E11 on another worksheet in the same workbook, you can use either of the following examples:
Application.Goto ActiveWorkbook.Sheets("Sheet3").Range("D3:E11")
Application.Goto ActiveWorkbook.Sheets("Sheet3").Range("D3", "E11")
Or, you can activate the worksheet, and then use method 4 above to select the range:
Sheets("Sheet3").Activate
ActiveSheet.Range(Cells(3, 4), Cells(11, 5)).Select
Select a Range of Cells on a Worksheet in a Different Workbook
⇧
To select the range E4:F12 on a worksheet in a different workbook, you can use either of the following examples:
Application.Goto Workbooks("BOOK2.XLS").Sheets("Sheet1").Range("E4:F12")
Application.Goto Workbooks("BOOK2.XLS").Sheets("Sheet1").Range("E4", "F12")
Or, you can activate the worksheet, and then use method 4 above to select the range:
Workbooks("BOOK2.XLS").Sheets("Sheet1").Activate
ActiveSheet.Range(Cells(4, 5), Cells(12, 6)).Select
Select a Named Range on the Active Worksheet
⇧
To select the named range "Test" on the active worksheet, you can use either of the following examples:
Range("Test").Select
Application.Goto "Test"
Select a Named Range on Another Worksheet in the Same Workbook
⇧
To select the named range "Test" on another worksheet in the same workbook, you can use the following example:
Application.Goto Sheets("Sheet1").Range("Test")
Or, you can activate the worksheet, and then use method 7 above to select the named range:
Sheets("Sheet1").Activate
Range("Test").Select
Select a Named Range on a Worksheet in a Different Workbook
⇧
To select the named range "Test" on a worksheet in a different workbook, you can use the following example:
Application.Goto Workbooks("BOOK2.XLS").Sheets("Sheet2").Range("Test")
Or, you can activate the worksheet, and then use method 7 above to select the named range:
Workbooks("BOOK2.XLS").Sheets("Sheet2").Activate
Range("Test").Select
Select a Cell Relative to the Active Cell
⇧
To select a cell that is five rows below and four columns to the left of the active cell, you can use the following example:
ActiveCell.Offset(5, -4).Select
To select a cell that is two rows above and three columns to the right of the active cell, you can use the following example:
ActiveCell.Offset(-2, 3).Select
Note
An error will occur if you try to select a cell that is "off the worksheet." The first example shown above will return an error if the active cell is in columns A through D, since moving four columns to the left would take the active cell to an invalid cell address.
Select a Cell Relative to Another (Not the Active) Cell
⇧
To select a cell that is five rows below and four columns to the right of cell C7, you can use either of the following examples:
ActiveSheet.Cells(7, 3).Offset(5, 4).Select
ActiveSheet.Range("C7").Offset(5, 4).Select
Select a Range of Cells Offset from a Specified Range
⇧
To select a range of cells that is the same size as the named range "Test" but that is shifted four rows down and three columns to the right, you can use the following example:
ActiveSheet.Range("Test").Offset(4, 3).Select
If the named range is on another (not the active) worksheet, activate that worksheet first, and then select the range using the following example:
Sheets("Sheet3").Activate
ActiveSheet.Range("Test").Offset(4, 3).Select
Select a Specified Range and Resize the Selection
⇧
To select the named range "Database" and then extend the selection by five rows, you can use the following example:
Range("Database").Select
Selection.Resize(Selection.Rows.Count + 5, Selection.Columns.Count).Select
Select a Specified Range, Offset It, and Then Resize It
⇧
To select a range four rows below and three columns to the right of the named range "Database" and include two rows and one column more than the named range, you can use the following example:
Range("Database").Select
Selection.Offset(4, 3).Resize(Selection.Rows.Count + 2, Selection.Columns.Count + 1).Select
Select the Union of Two or More Specified Ranges
⇧
To select the union (that is, the combined area) of the two named ranges "Test" and "Sample," you can use the following example:
Application.Union(Range("Test"), Range("Sample")).Select
Note that both ranges must be on the same worksheet for this example to work. Note also that the Union method does not work across sheets. For example, this line works fine.
Set y = Application.Union(Range("Sheet1!A1:B2"), Range("Sheet1!C3:D4"))
but this line
Set y = Application.Union(Range("Sheet1!A1:B2"), Range("Sheet2!C3:D4"))
returns the error message:
Union method of application class failed
Select the Intersection of Two or More Specified Ranges
⇧
To select the intersection of the two named ranges "Test" and "Sample," you can use the following example:
Application.Intersect(Range("Test"), Range("Sample")).Select
Note that both ranges must be on the same worksheet for this example to work.
Examples 17-21 in this article refer to the following sample set of data. Each example states the range of cells in the sample data that would be selected.
A1: Name B1: Sales C1: Quantity
A2: a B2: $10 C2: 5
A3: b B3: C3: 10
A4: c B4: $10 C4: 5
A5: B5: C5:
A6: Total B6: $20 C6: 20
Select the Last Cell of a Column of Contiguous Data
⇧
To select the last cell in a contiguous column, use the following example:
ActiveSheet.Range("a1").End(xlDown).Select
When this code is used with the sample table, cell A4 will be selected.
Select the Blank Cell at Bottom of a Column of Contiguous Data
⇧
To select the cell below a range of contiguous cells, use the following example:
ActiveSheet.Range("a1").End(xlDown).Offset(1,0).Select
When this code is used with the sample table, cell A5 will be selected.
Select an Entire Range of Contiguous Cells in a Column
⇧
To select a range of contiguous cells in a column, use one of the following examples:
ActiveSheet.Range("a1", ActiveSheet.Range("a1").End(xlDown)).Select
-or-
ActiveSheet.Range("a1:" & ActiveSheet.Range("a1"). End(xlDown).Address).Select
When this code is used with the sample table, cells A1 through A4 will be selected.
Select an Entire Range of Non-Contiguous Cells in a Column
⇧
To select a range of cells that are non-contiguous, use one of the following examples:
ActiveSheet.Range("a1",ActiveSheet.Range("a" & ActiveSheet.Rows.Count).End(xlUp)).Select
-or-
ActiveSheet.Range("a1:" & ActiveSheet.Range("a" & ActiveSheet.Rows.Count). End(xlUp).Address).Select
When this code is used with the sample table, it will select cells A1 through A6.
Select a Rectangular Range of Cells
⇧
In order to select a rectangular range of cells around a cell, use the CurrentRegion method. The range selected by the CurrentRegion method is an area bounded by any combination of blank rows and blank columns. The following is an example of how to use the CurrentRegion method:
ActiveSheet.Range("a1").CurrentRegion.Select
This code will select cells A1 through C4. Other examples to select the same range of cells are listed below:
ActiveSheet.Range("a1", ActiveSheet.Range("a1").End(xlDown).End(xlToRight)).Select
-or-
ActiveSheet.Range("a1:" & ActiveSheet.Range("a1").End(xlDown).End(xlToRight).Address).Select
In some instances, you may want to select cells A1 through C6. In this example, the CurrentRegion method will not work because of the blank line on Row 5. The following examples will select all of the cells:
lastCol = ActiveSheet.Range("a1").End(xlToRight).Column
lastRow = ActiveSheet.Cells(ActiveSheet.Rows.Count, lastCol).End(xlUp).Row
ActiveSheet.Range("a1", ActiveSheet.Cells(lastRow, lastCol)).Select
-or-
lastCol = ActiveSheet.Range("a1").End(xlToRight).Column
lastRow = ActiveSheet.Cells(ActiveSheet.Rows.Count, lastCol).End(xlUp).Row
ActiveSheet.Range("a1:" & ActiveSheet.Cells(lastRow, lastCol).Address).Select
Select Multiple Non-Contiguous Columns of Varying Length
⇧
To select multiple non-contiguous columns of varying length, use the following sample table and macro example:
A1: 1 B1: 1 C1: 1 D1: 1
A2: 2 B2: 2 C2: 2 D2: 2
A3: 3 B3: 3 C3: 3 D3: 3
A4: B4: 4 C4: 4 D4: 4
A5: B5: 5 C5: 5 D5:
A6: B6: C6: 6 D6:
StartRange = "A1"
EndRange = "C1"
Set a = Range(StartRange, Range(StartRange).End(xlDown))
Set b = Range(EndRange, Range(EndRange).End(xlDown))
Union(a,b).Select
When this code is used with the sample table, cells A1:A3 and C1:C6 will be selected.
⇧
Excel drop down list, aka dropdown box or dropdown menu, is used to enter data in a spreadsheet from a predefined items list.
When you select a cell containing the list, a small arrow appears next to the cell, so you click on it to make a selection.
The main purpose of using drop down lists in Excel is to limit the number of choices available for the user.
Apart from that, a dropdown prevents spelling mistakes and makes data input faster and more consistent.
How to create drop down list in Excel
⇧
To make a drop-down list in Excel, use the Data Validation feature.
Here are the steps:
Select one or more cells where you want the picklist to appear.
This can be a single cell, a range of cells, or a whole column.
To select multiple non-contiguous cells, press and hold the Ctrl key.
On the Data tab, in the Data Tools group, click Data Validation.
On the Settings tab of the Data Validation dialog box, do the following:
In the Allow box, select List.
In the Source box, type the items separated by a comma with or without spaces.
Or select a range of cells on the sheet containing the items.
Make sure the In-cell dropdown box is checked (default), otherwise the drop-down arrow won't appear next to the cell.
Select or clear the Ignore blank option depending on how you want to handle empty cells.
When done, click OK.
Congratulations! You have successfully created a simple dropdown list in Excel.
Now, your users can click an arrow next to a cell, and then select the entry they want.
A drop down list of comma separated values works well for small data validation lists that are unlikely to ever change.
For frequently updated lists, you'd better use a range or table for the source.
The detailed step-by-step instructions for each method follow below.
Tip. To expedite data input in your Excel sheets, you can also use a data entry form.
Make drop-down menu from a range of cells
⇧
To insert a drop-down list based on the values input in a range of cells, carry out these steps:
Start by creating a list of items that you want to include in the drop-down.
For this, just type each item in a separate cell.
This can be done in the same worksheet as the dropdown list or in a different sheet.
Select the cell(s) that are to contain the list.
On the ribbon, click the Data tab > Data Validation.
In the Data Validation dialog window, select List from the Allow drop-down menu.
Place the cursor in the Source box and select the range of cells containing the items, or click the Collapse Dialog icon and then select the range.
When done, click OK.
Advantages: You can modify your dropdown list by making changes in the referenced range without having to edit the data validation list itself.
Drawbacks: To add or remove items, you will need to update the Source range reference.
Insert drop down list from a named range
⇧
Initially, this method of creating an Excel data validation list takes a bit more time but may save even more time in the long run.
Make a list of items on the sheet.
The values should be entered into a single column or row without any blank cells.
Tip. It's a good idea to sort the items alphabetically or in a custom order you want them to appear in the drop-down menu.
Create a named range.
The fastest way is to select the cells and type the desired name directly in the Name Box.
When finished, click Enter to save the newly created named range.
For more information, please see how to define a name in Excel.
As an example, let's create a range named Ingredients:
Select the cells for the picklist - on the same sheet as the named range or in a different worksheet.
Open the Data Validation dialog window and configure the rule:
In the Allow box, select List.
In the Source box, type an equals sign followed by the range name.
In our case, it's =Ingredients.
Click OK.
Note. If your named range has at least one blank cell, leaving the Ignore blank box selected allows typing any value in the validated cell.
Advantages: If you insert multiple drop-downs in different sheets, named ranges will make them a lot easier to identify and manage.
Drawbacks: Takes a bit more time to set up.
Create drop-down from Excel table
⇧
Instead of using a named range, you can place the source data into a fully functional Excel table.
Why may you want to use a table? First and foremost, because it lets you create an expandable dynamic drop-down list that updates automatically as you add or remove items to/from the table.
To make a dynamic dropdown from an Excel table, follow these steps:
Type the list items in a table or convert an existing range to a table using the Ctrl + T shortcut.
Select the cell(s) where you wish to insert a dropdown.
Open the Data Validation dialog window.
Select List from the Allow drop-down box.
In the Source box, enter the formula referring to a specific column in your table, not including the header cell.
For this, use the INDIRECT function with a structured reference like this:
=INDIRECT("Table_name[Column_name]")
When done, click OK.
For this example, we're making a dropdown menu from the column named Ingredients in Table1:
=INDIRECT("Table1[Ingredients]")Advantages: Easy and quick way to insert an expandable dynamic drop down menu in Excel.
Drawbacks: Not found :)
How to create a dynamic dropdown list in Excel
⇧
If you regularly change the items in your picklist, the best approach is to create a dynamic drop down list.
In this case, the list will update automatically in all the cells that contain whenever you add or remove items to/from the source list.
The fastest way to make a dynamic drop down in Excel is from a table as shown above.
That is the default behavior of Excel tables; no extra settings or moves are required.
Another way is to use a regular named range and reference it with the OFFSET formula, as explained below.
Type the items for the drop down menu in separate cells.
Create a named formula.
For this, press Ctrl + F3 to open the New Name dialog box.
Type the name you want in the Name box, and then enter the following formula in the Refers to box.
=OFFSET(Sheet3!$A$2, 0, 0, COUNTA(Sheet3!$A:$A), 1)
Where:
Sheet3 - the sheet's name
A - the column where the drop-down items are located
How this formula works
The formula comprises two functions - OFFSET and COUNTA.
The COUNTA function counts all non-blanks in the specified column.
OFFSET uses that count for the height argument, so it returns a reference to a range that includes only non-empty cells, starting from the cell containing the first item that you supply for the reference argument.
Advantages: The main advantage of a dynamic drop-down list is that you won't have to change the reference to the named range each time the source list is expanded or contracted.
You simply delete or type new entries in the source list, and your dropdown menu will update automatically!
Drawbacks: A bit complex setup process.
Make a dynamic dropdown list in Excel 365/2021
⇧ Dynamic Array Excel has many innovative functions that are not available in older versions.
One of these new functions named UNIQUE can help you create a dynamic drop-down with a simple formula.
Suppose you have a dataset with many repeated items like in column A in the image below.
You aim to add a dropdown list where each item appears just once.
To extract the unique items, use this formula:
=UNIQUE(A2:A21)
Optionally, you can sort the extracted values alphabetically by wrapping it in the SORT function:
=SORT(UNIQUE(A2:A21))
This dynamic array formula is entered just in one cell (E2) and it automatically spills into as many cells as needed to show all the unique items.
Next, you set up a drop down list using a spill range reference, which is a cell address followed by a hash character.
In our case it's =$E$2# or =Sheet1!$E$2# if a dropdown is in another sheet:
The result is an expandable dynamic drop-down list - the UNIQUE function automatically extracts new items as they are added to the source table, and the spill range reference forces Excel to update the drop-down list accordingly.
Tip. The same approach can be used to create a cascading drop-down list in Excel 365. For full details, please see Make a dynamic dependent dropdown list an easy way.
How to create drop down list from another sheet
⇧
To insert a drop-down menu that pulls data from a different worksheet, you can use a normal range, named range or Excel table:
When creating a drop down list from a table, no extra steps are needed as table names/references are valid across the entire workbook.
If you insert a drop down from a regular range, include the sheet's name in the source reference.
In the Data Validation dialog window, place the cursor in the Source box, switch to the other sheet and select the range containing the items.
Excel will add the sheet name to the reference automatically.
How to make drop-down list from another workbook
⇧
To create a drop-down menu in Excel using a list from another workbook as the source, you will have to define 2 named ranges - one in the source workbook and the other in the workbook where you wish to insert your Data Validation list.
The steps are:
In the source workbook, create a named range for the source list, say Source_list.
In the main workbook, define a name that references your source list.
For this example, we create the name Items that refers to:
=SourceFile.xlsx!Source_list
If the workbook's name contains spaces or non-alphabetical characters, it must be enclosed in single quotation marks like this:
='Source File.xlsx'!Source_list
For more details, please see How to make external reference in Excel.
In the main workbook, select the cell(s) for your picklist and click the Data tab > Data Validation.
In the Source box, reference the name you created in step 2. In our case, it's =Items.
Notes:
For the drop-down list from another workbook to work, the source workbook must be open.
The dropdown list created in this way won't update automatically when items are added to or removed from the source list - you will have to modify the source list reference manually.
How to make a dynamic dropdown from another workbook
⇧
To create a dynamic dropdown list from another workbook, define a formula name in the source workbook using the OFFSET formula explained in Creating a dynamic drop-down in Excel.
In this case, a dropdown menu in another workbook will be updated on the fly once any changes are made to the source list.
Searchable drop down list in Excel 365
⇧
In Excel 365, data validation lists have an awesome AutoComplete feature.
To speed up data entry in large lists, just start typing the target word in the dropdown menu cell - the autocomplete algorithm will match the typed substring with the dropdown list items and show you the found matches.
As you type more characters, the displayed list is narrowed down, and conversely, when you remove characters, more matches are shown.
Insert a drop down list with message
⇧
To show an information message when someone clicks a dropdown list cell, proceed in this way:
In the Data Validation dialog box, switch to the Input Message tab.
Make sure the Show input message when cell is selected option is checked.
Type the title and message in the corresponding fields (up to 225 characters).
Click OK to save the message and close the dialog.
The resulting drop down list with message will look similar to this:
Make an editable drop down list in Excel
⇧
By default, an Excel drop-down is non-editable, i.e. restricted to the values in the list itself.
If you type any other value, an error alert will show up.
However, you can allow users to enter their own values.
Here's how:
Open the Data Validation dialog window.
On the Error Alert tab, uncheck the Show error alert after invalid data is entered box.
Technically, this turns a drop-down list into a combo box.
The term "combo box" means an editable dropdown that allows users to either select a value from the predefined list or type a custom value directly in the box.
Optionally, you can display a warning message when someone attempts to enter a value that is not in the list:
On the Error Alert tab, select the Show error alert after invalid data is entered option.
From the Style box, pick either Information or Warning, and then type the title and message text.
Information message is best to be used if there is nothing wrong with the user entering a custom value.
Warning message will induce users to select an item from the drop-down box rather than enter their own data, though it does not prohibit it.
And here's an editable Excel dropdown list with a warning message in action:
Tip. If you are not sure what title or message text to type, you can leave the fields empty.
In this case, Excel will display the default alert "This value does not match the data validation restrictions defined for this cell."
cascading (dependent) drop down list with conditional Data Validation.
Notes on the examples
The ActiveSheet property can usually be omitted, because it is implied if a specific sheet is not named. For example, instead of
ActiveSheet.Range("D5").Select
you can use:
Range("D5").Select
The ActiveWorkbook property can also usually be omitted. Unless a specific workbook is named, the active workbook is implied.
When you use the Application.Goto method, if you want to use two Cells methods within the Range method when the specified range is on another (not the active) worksheet, you must include the Sheets object each time. For example:
With ActiveWorkbook.Sheets("Sheet1")
Application.Goto .Range(.Cells(2, 3), .Cells(4, 5))
End With
For any item in quotation marks (for example, the named range "Test"), you can also use a variable whose value is a text string. For example, instead of
ActiveWorkbook.Sheets("Sheet1").Activate
you can use
ActiveWorkbook.Sheets(myVar).Activate
where the value of myVar is "Sheet1".
1. Open up your workbook
2. Get into VB Editor (Press Alt+F11)
3. Insert a new module (Insert > Module)
4. Copy and Paste in your code
example: IF(ISNA(VLOOKUP(A2,$AA$1:$AB$99,2,False)),"This employee has left the company.", VLOOKUP(A2,$AA$1:$AB$99,2,False))
5. Get out of VBA (Press Alt+Q)
6. Save your sheet
Create an external reference (link) to a cell in another workbook
⇧
You can refer to the contents of cells in another workbook by creating an external reference formula.
An external reference (also called a link) is a reference to a cell or range on a worksheet in another Excel workbook, or a reference to a defined name in another workbook.
Sub ExtractData()
Application.ScreenUpdating = False
Set wb = Workbooks.Open("d:\我的文档\桌面\excel video\资料库.xls")
For row = 1 To 4
Workbooks("测试工作簿.xls").Worksheets("订单").Cells(row, 3).Value = wb.Worksheets("资料库").Cells(row, 2)
Next row
wb.Close SaveChanges:=False
Application.ScreenUpdating = True
End Sub
Create an external reference between cells in different workbooks
⇧
Open the workbook that will contain the external reference (the destination workbook) and the workbook that contains the data that you want to link to (the source workbook).
Select the cell or cells where you want to create the external reference.
Type = (equal sign).
If you want to use a function, such as SUM, then type the function name followed by an opening parenthesis.
For example, =SUM(.
Switch to the source workbook, and then click the worksheet that contains the cells that you want to link.
Select the cell or cells that you want to link to and press Enter.
Note: If you select multiple cells, like =[SourceWorkbook.xlsx]Sheet1!$A$1:$A$10, and have a current version of Microsoft 365, then you can simply press ENTER to confirm the formula as a dynamic array formula.
Otherwise, the formula must be entered as a legacy array formula by pressing CTRL+SHIFT+ENTER.
For more information on array formulas, see Guidelines and examples of array formulas.
Excel will return you to the destination workbook and display the values from the source workbook.
Note that Excel will return the link with absolute references, so if you want to copy the formula to other cells, you'll need to remove the dollar ($) signs:
=[SourceWorkbook.xlsx]Sheet1!$A$1
If you close the source workbook, Excel will automatically append the file path to the formula:
='C:\Reports\[SourceWorkbook.xlsx]Sheet1'!$A$1
Create an external reference to a defined name in another workbook
⇧
Open the workbook that will contain the external reference (the destination workbook) and the workbook that contains the data that you want to link to (the source workbook).
Select the cell or cells where you want to create the external reference.
Type = (equal sign).
Switch to the source workbook, and then click the worksheet that contains the cells that you want to link.
Press F3, select the name that you want to link to and press Enter.
Note: If the named range references multiple cells, and you have a current version of Microsoft 365, then you can simply press ENTER to confirm the formula as a dynamic array formula.
Otherwise, the formula must be entered as a legacy array formula by pressing CTRL+SHIFT+ENTER.
For more information on array formulas, see Guidelines and examples of array formulas.
Excel will return you to the destination workbook and display the values from the named range in the source workbook.
Define a name that contains an external reference to cells in another workbook
⇧
Open the destination workbook and the source workbook.
In the destination workbook, Go to Formulas > Defined Names > Define Name.
In the New Name dialog box, in the Name box, type a name for the range.
In the Refers to box, delete the contents, and then keep the cursor in the box.
If you want the name to use a function, enter the function name, and then position the cursor where you want the external reference.
For example, type =SUM(), and then position the cursor between the parentheses.
Switch to the source workbook, and then click the worksheet that contains the cells that you want to link.
Select the cell or range of cells that you want to link, and click OK.
Where external references can be used effectively
⇧
External references are especially useful when it's not practical to keep large worksheet models together in the same workbook.
Merge data from several workbooksYou can link workbooks from several users or departments and then integrate the pertinent data into a summary workbook.
That way, when the source workbooks are changed, you won't have to manually change the summary workbook.
Create different views of your dataYou can enter all of your data into one or more source workbooks, and then create a report workbook that contains external references to only the pertinent data.
Streamline large, complex modelsBy breaking down a complicated model into a series of interdependent workbooks, you can work on the model without opening all of its related sheets.
Smaller workbooks are easier to change, don't require as much memory, and are faster to open, save, and calculate.
What an external reference to another workbook looks like
⇧
Formulas with external references to other workbooks are displayed in two ways, depending on whether the source workbook — the one that supplies data to a formula — is open or closed.
When the source is open, the external reference includes the workbook name in square brackets ([ ]), followed by the worksheet name, an exclamation point (!), and the cells that the formula depends on.
For example, the following formula adds the cells C10:C25 from the workbook named Budget.xls.
External reference
=SUM([Budget.xlsx]Annual!C10:C25)
When the source is not open, the external reference includes the entire path.
External reference
=SUM('C:\Reports\[Budget.xlsx]Annual'!C10:C25)
Note: If the name of the other worksheet or workbook contains spaces or non-alphabetical characters, you must enclose the name (or the path) within single quotation marks as in the example above.
Excel will automatically add these for you when you select the source range.
Formulas that link to a defined name in another workbook use the workbook name followed by an exclamation point (!) and the name.
For example, the following formula adds the cells in the range named Sales from the workbook named Budget.xlsx.
External reference
=SUM(Budget.xlsx!Sales)
Create a link to another worksheet
⇧
Select the cell or cells where you want to create the external reference.
Type = (equal sign).
If you want to use a function, such as SUM, then type the function name followed by an opening parenthesis.
For example, =SUM(.
Switch to the worksheet that contains the cells that you want to link to.
Select the cell or cells that you want to link to and press Enter.
Note: If you select multiple cells (=Sheet1!A1:A10), and have a current version of Microsoft 365, then you can simply press ENTER to confirm the formula as a dynamic array formula.
Otherwise, the formula must be entered as a legacy array formula by pressing CTRL+SHIFT+ENTER.
For more information on array formulas, see Guidelines and examples of array formulas.
Excel will return to the original worksheet and display the values from the source worksheet.
Create an external reference between cells in different workbooks
⇧
Open the workbook that will contain the external reference (the destination workbook, also called the formula workbook) and the workbook that contains the data that you want to link to (the source workbook, also called the data workbook).
In the source workbook, select the cell or cells you want to link.
Press Ctrl+C or go to Home > Clipboard > Copy.
Switch to the destination workbook, and then click the worksheet where you want the linked data to be placed.
Select the cell where you want to place the linked data, then go to Home > Clipboard > Paste > Paste Link.
Excel will return the data you copied from the source workbook.
If you change it, it will automatically change in the destination workbook when you refresh your browser window.
To use the link in a formula, type = in front of the link, choose a function, type (, and then type ) after the link.
Create a link to a worksheet in the same workbook
⇧
Select the cell or cells where you want to create the external reference.
Type = (equal sign).
If you want to use a function, such as SUM, then type the function name followed by an opening parenthesis.
For example, =SUM(.
Switch to the worksheet that contains the cells that you want to link to.
Select the cell or cells that you want to link to and press Enter.
Excel will return to the original worksheet and display the values from the source worksheet.
Excel VBA, Get Data From Another Workbook Through Automation
Basic object model of an Excel application:
Application: As you can see at the top of the hierarchy there is the Excel application.
Each application can have several workbooks.
Workbooks: Excel files are workbooks.
You can have several excel workbooks (files) open at the same time, but there will only be one Excel application open.
You can test this by looking at the task manager.
Worksheet: Each workbook consists of at least one worksheet.
By default workbooks have 3 worksheets (Sheet1, Sheet2, Sheet3).
Method 1, Adding a Workbook to the Current Application:
In this method a new workbook will be added to the collection of workbooks of the current Excel application.
In other words we will not be creating a new Excel application object, but only adding a new workbooks to its collection of workbooks.
This is done through the code below:
Sub main()
Dim wb As Workbook
Set wb = Workbooks.Open( "D:StuffBusinessTempData.xlsx")
End Sub
Where "D:StuffBusinessTempData.xlsx" is the path of the second excel workbook.
Assume the following data is in sheet1 of the second workbook:
The code below will open the file "Data.xlsx", read the first column of data, and close the file:
Sub Example1()
Dim wb As Workbook
Dim i As Integer
'open the workbook with data
Set wb = Workbooks.Open( "D:StuffBusinessTempData.xlsx")
'read the data from the first columns
For i = 1 To 9
Cells(i, 1) = wb.Worksheets("Sheet1").Cells(i + 1, 1)
Next i
'close the workbook
wb.Close
End Sub
Note how the cells in the second workbook were referenced using their complete name:
wb.Worksheets("Sheet1").Cells(i + 1, 1)
We could have also chosen to use this:
wb.Worksheets(1).Cells(i + 1, 1)
Result:
Note: The code below would have resulted in an error:
wb.Sheet1.Cells(i + 1, 1)
Method 2, Creating a New Excel Application:
The method explained in the previous section has its pros and cons:
Pros: It is fast.
Cons: A workbook will flash on the screen and disappear after closing.
Having a workbook flash on the screen and disappear might not be a very professional thing to see in a program.
One method to overcome this is to do the following:
Automate a new Excel application object.
Sets its visible property to "hidden".
Open the second workbook from the new excel application object.
The problem with this method is that the program will pause a little until the new excel application is automated.
Sub Example2()
Dim appExcel As Application
Dim wb As Workbook
Dim i As Integer
'create new excel application object
Set appExcel = New Application
'set the applications visible property to false
appExcel.Visible = False
'open the workbook with data
Set wb = appExcel.Workbooks.Open( "D:StuffBusinessTempData.xlsx")
End Sub
This is assuming the second file is located in the path "D:StuffBusinessTempData.xlsx".
The complete code can be seen below:
Sub Example3()
Dim appExcel As Application
Dim wb As Workbook
Dim i As Integer
'create new excel application object
Set appExcel = New Application
'set the applications visible property to false
appExcel.Visible = False
'open the workbook with data
Set wb = appExcel.Workbooks.Open( "D:StuffBusinessTempData.xlsx")
For i = 1 To 9
Cells(i, 1) = wb.Worksheets("Sheet1").Cells(i + 1, 1)
Next i
'close the workbooks
wb.Close
'close the application
appExcel.Quit
End Sub
Note: Don't forget to close the excel application object or you will end up with resource leakage.
Step 1: Enable the VBA Debugger
Choose Options from the Tools menu.
Check the box next to "Break on All Errors" in the General tab of the Options dialogue box.
Step 2: Add Breakpoints
Click on the line of code where you wish to halt the execution, then press F9 to add a breakpoint. You can also choose "Toggle Breakpoint" from the context menu when you right-click the line of code.
Step 3: Run the Code
Press F5 or choose the Run option in the VBA editor to accomplish this.
Step 4: Examine Variable Values
Check the values of variables or objects in the Locals pane when the code reaches a breakpoint.
Hovering the mouse over a particular variable
Hovering the mouse over a particular variable or expression in the code or typing it into the Immediate window will also reveal its value.
Step 5: Stepping through Code
You can walk through the code by pressing F8 or the Step Into button in the VBA editor.
Step 6: Using the Immediate Window
A useful tool for evaluating variable values and testing expressions is the Instant window.
You can access the Immediate window by hitting Ctrl + G or by choosing Immediate Window from the View menu. In addition, you can run code and test expressions in the Instant window.
Step 7: Error Handling
When an error occurs, error handling is essential for keeping the code from crashing. With the On Error statement, you may add error management to your code.
You can indicate what should happen when an error arises using the On Error statement, such as displaying a message or logging the error.
Best practices for debugging VBA code
Programming with VBA requires debugging VBA code. Thus best practices must be followed to enable successful and quick debugging.
Use Descriptive Variable Names
The function of each variable in your code can be more recognized and understood if you give them descriptive names.
This makes it simpler to pinpoint which variable might be the source of an error, which is useful for debugging your code.
Use Option Explicit
To ensure that all variables used in your code are declared, use Option Explicit at the beginning of your code.
By doing this, it is possible to avoid typos and other problems when using undeclared variables.
Break Your Code into Smaller Sections
It may be simpler to locate an error if your code is divided into smaller portions.
You can reduce the number of potential mistakes caused by isolating particular portions of your code. It will make it simpler to discover and solve the error.
Use Error Handling
When an error occurs, using error handling can assist in keeping your program from crashing.
Although it might be challenging to pinpoint the exact location of a mistake, this can be especially helpful when working with big and complicated codebases.
Use Breakpoints
Breakpoints make it possible to halt the execution of your code at particular points, making it simpler to check the values of variables and expressions there.
This can be helpful when you need to locate an error or check the values of particular variables while the code is executed.
Stepping Through Code
By running your code one line at a time while stepping through it, you can check the execution path and see mistakes more easily.
To access an SQL Server
First, you need a data connection.
If you are using a work SQL Server, then you will be given details of your server by your IT department.
This will include:
The Server Name: It can also take this from the Connection String if you have it.
Authentication Method: You will use either:
Windows Authentication, using your Windows username and password
SQL Server Authentication, using a separate username and password
If you have Microsoft SQL Server on your own computer, then the server name could be “localhost" or “.", and you will probably use Windows Authentication.
You can use this connection to retrieve the Microsoft SQL Server data.
There are three different places in Excel where you can load SQL data:
In the main Excel window
In the Get and Transform window (also known as the Power Query editor)
In the Power Pivot window (also known as the Data Model)
We will have a look at each of these places.
Connecting SQL to the main Excel window
The main Excel window is the one you use every time you open Excel.
To load data from SQL Server, go to Data – Get Data – From Database – From SQL Server Database.
You will then have to provide the Server Name.
There are four SQL Server data sources that you could query to return the results.
You may want the data from a table.
You may want the query results from a previously created view.
You may want the results from a stored procedure.
You may want to run an ad hoc SQL query using the SELECT statement.
If you want to run a Stored Procedure or an ad hoc query, then at this stage, you will need to click on “Advanced options" and write the query in the box provided.
You will also need to enter the name of the database as well.
Next, you need to provide the Authentication mode and any credentials required:
If you want to retrieve the results of a table or query, you can select the table or query.
If you then click “Load," it will be loaded into your Excel Workbook.
Once you have made the link, it will load the data into an Excel Table.
You can then use it just like other data stored in a table.
You can refresh the data whenever you want by right-hand clicking inside the table and choosing Refresh, or by going to Table Design – Refresh.
Connecting SQL to Get and Transform
The second way to connect to SQL data is by using the Get and Transform window.
This follows the same process for connecting to SQL Server as mentioned above, except that you press “Transform Data" instead of Load.
Once you have done this, then the data is in the Get and Transform window, also known as the Power Query Editor.
You can also load data directly from the Power Query Editor.
To do this, go to Home – New Source.
You can then perform additional manipulations before the data transfer into Excel.
For example, you might want to:
Hide some columns or rows (by going to Home – Choose/Remove Columns)
Add additional columns using formulas.
(However, Power Query uses a language called M, which differs significantly from Excel.)
Summarise the data using the Group By function
If you do this in Power Query, it will reduce the amount of data that goes into Excel.
Power Query reduces the amount of data that it receives from SQL Server through a process called Query Folding.
For example, you could retrieve all the contents of a table into Power Query, limit the number of rows to just 50, and reduce the number of columns used to just two.
This reduction will be incorporated into the SQL statement so that Excel only retrieves the needed rows and columns from SQL Server.
This reduces network traffic and increases the speed of retrieving that data.
When you leave the Power Query window by going to Home – Close & Load, it would then load the data into an Excel Table as before.
However, if you go to “Home – Close & Load To…" instead, you could then:
Use it in a Pivot Table or Chart without loading the data in Excel as a Table.
Save it as a Connection (without loading the data into an Excel Table).
If you save it as a connection, you can use it later as the data source in any new Pivot Tables.
In “Save & Load To…", there is a checkbox for “Add this data to the Data Model." If you click on this, Excel will then export the data into Power Pivot, also known as the Data Model.
We'll have a look at the Data Model in the next part of this Article.
Connecting SQL to Power Pivot
The third way of connecting SQL to Excel directly is by using the Data Model, also known as Power Pivot.
To open the Data Model, you need to go to Data – Manage Data Model.
Then you can import the data into Power Pivot by going to Home – Get External Data – From Database – From SQL Server.
You then connect to SQL Server in a similar process as before.
Once you have imported the data, you can then create calculation columns or measures.
Power Pivot uses a formula language called DAX to build formulas.
DAX is an extended version of the Excel formulas.
Once you have finished, you can then create a Power Pivot Table by going to Home – PivotTable – PivotTable.
This allows you to create Pivot Tables or charts from this data.
run a Macro by clicking a specific cell
Option Explicit
Sub Worksheet_SelectionChange(ByVal Target As Range)
If Selection.Count = 1 Then
If Not Intersect(Target, Range("D4")) Is Nothing Then
Call MyMacro
End If
End If
End Sub
Automatically open a specific worksheet when open workbook
double click This Workbook to open the Module,
and then copy and paste the following VBA code in the right pane.
Private Sub Workbook_Open()
Sheets("开始").Activate
End Sub
Private Sub Workbook_Open()
Application.WindowState = xlMaximized
End Sub
Jump To Sheet
Jump to the first worksheet of the workbook
Sub GoToFirstSheet()
On Error Resume Next
Sheets(1).Select
End Sub
Jump to the last worksheet of the workbook
Sub GoTolastSheet()
On Error Resume Next
Sheets(Sheets.Count).Select
End Sub
Jump To Sheet
Sub JumpToSheet()
Dim FindName As String, FindSheet As Worksheet
FindName = InputBox(prompt:="Enter the sheet name that you need to find", Title:=" jump to Specific Sheet "")
For Each FindSheet In ActiveWorkbook.Worksheets
If FindSheet.Name = FindName Then
FindSheet.Activate
Exit Sub
End If
Next
End Sub
Sub JumpTo派工单()
Sheets("派工单").Activate
End Sub
declare a Public variable
Place variable in the Declarations section of VBA code below the Option Explicit statement, outside of any of your Sub Procedures or Functions and you also have to use the Public keyword.
Public Number As Integer ' Public Integer variable.
Public NameArray(1 To 5) As String ' Public array variable.
' Multiple declarations, two Variants and one Integer, all Public.
Public MyVar, YourVar, ThisVar As Integer
Sub ExtractData()
Application.ScreenUpdating = False '停止屏幕更新 避免减低运行速度
Dim 横 As Integer '定义 横 变量
Dim wb As Workbook '定义 wb 变量，工作簿
Set wb = Workbooks.Open("d:\我的文档\桌面\excel video\资料库.xls") '打开外部工作簿
For 横 = 1 To 4
Cells(横, 3).Value = wb.Worksheets("资料库").Cells(横, 2) '将外部工作簿数据填入目前工作表
Next 横
wb.Close SaveChanges:=False '关闭外部工作簿 不更新
Application.ScreenUpdating = True '从新更新屏幕
End Sub
读入外部工作簿 抽出数据 填入目前工作表 使用 range 范围
Sub testData()
Application.ScreenUpdating = False
Set wb = Workbooks.Open("d:\我的文档\桌面\excel video\资料库.xls")
'将 wb "a2" 数据填入目前工作表"订单" "A2"范围
ThisWorkbook.Sheets("订单").Range("A2") = wb.Range("a2")
wb.Close SaveChanges:=False '关闭外部工作簿 不更新
Application.ScreenUpdating = True '从新更新屏幕
End Sub
重置（清空）范围 使用 ClearContents
Sub reset()
Worksheets("订单").Range("B9:c15").ClearContents
End Sub
建立模组後 就建立sub 宏
建立重设表格 sub
sub 重设表格()
sheets("订单").range("d5, d10:e16").ClearContents
end sub
sheets("订单") 指定一个工作表
range("d5, d10:e16") 指定一个范围 要用引号框住 中间可以有多个逗号指向各个不同区域
ClearContents 是清除单元格内容
点 . 用来连接多个函数
按F5 可以立即执行宏 可以测试是否有效
用vba 来取得范围内最後一行数据
CurrentRegion 目前范围
Range("A1").CurrentRegion
End 末端
Range("A1").CurrentRegion.End(xlToRight) '在A1单元格范围 移动到最右边
Range("A1").CurrentRegion.End(xlDown) '在A1单元格范围 移动到最下边
定义一个变数来储存一个未知数值
dim 目前编号 as integer ' 定义一个变数叫 目前编号, 这是一个整数
目前编号 = sheets("资料库").range("a1").CurrentRegion.End(xlDown).value ' value 是单元格的值
sheets("订单").range("G6").value = 目前编号 + 1
sheets("订单").range("G7").value = date ' date 是日期函数
把工作表上的按钮指定向一个宏
点按按钮就可以执行一组动作 叫宏 或者叫巨集 或者叫功能
带有宏的工作簿 要储存为启用巨集的工作簿
Copy a Range using Variable
declare a variable as a range
declared the range A1:A10 as a variable rng
copy it using the following code
Sub range_variable()
Dim rng As Range
Set rng = Range("b3:g24")
rng.Copy
End Sub
copy from the range B:G in the last row of column B,
and paste that into B:G the next blank row in column B
to the last row number in A, 0
lrB = cells (rows.count, "B").End(xlUp).Row
Range("B" & lrB & ":G" & lrB).copy destination:=range ("B" & lrB +1 &":G" & lr)
Dim lr As Long
lr = Cells(Rows.Count, "A").End(xlUp).Row
Range("b2:G2").Copy Destination:=Range("B3:G" & lr)
Sub Copy_Range()
Range("B5:C7").Copy Range("F5:G7")
End Sub
Sub CopyAndPaste()
ActiveSheet.Range("a1").Select
Selection.End(xlDown).Select
lastRow = ActiveCell.Row + 1
lastCell = "A" & lastRow
ActiveSheet.Range("a1").Select
ActiveSheet.Range(Selection, Selection.End(xlToRight)).Select
Selection.Copy
ActiveSheet.Range(lastCell).Select
ActiveSheet.Paste
End Sub
Sub DeclareAndSettingRange()
Dim rng As Range
Set rng = Range("A10:B10")
rng.Value = "AutomateExcel"
rng.Font.Bold = True
rng.Select
rng.Columns.AutoFit
End Sub
Using Range Variable in a Function
Sub vba_range_variable()
Dim iNames As Variant
Dim rng As Range
Set rng = Range("A1:A10")
Range("B1") = WorksheetFunction.Max(rng)
End Sub
Count Number of Rows and Columns
Sub range_variable()
Dim rng As Range
Set rng = Range("A1:A10")
MsgBox "This range has " & rng.Rows.Count & " row(s) and " & rng.Columns.Count & " coulmn(s)."
End Sub
Sub 复制()
Set rng = Range("b3:g24")
rng.Copy
Range("临时!b3:g24").PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks:=False, Transpose:=False
End Sub
Sub 粘贴()
Set rng = Range("临时!b3:g24")
rng.Copy
Range("入库登记!b3:g24").PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks:=False, Transpose:=False
End Sub
thisRange = cells(rows.count, "B").End(xlUp).Row
Range("B" & thisRange & ":G" & thisRange).copy destination:=range ("B" & thisRange +1 &":G" & lr)
Dim lr As Long
lr = Cells(Rows.Count, "A").End(xlUp).Row
Range("b2:G2").Copy Destination:=Range("B3:G" & lr)
Excel WorksheetFunction
WorksheetFunction is an Excel worksheet functions that can be called from Visual Basic.
Set myRange = Worksheets("Sheet1").Range("A1:C10")
answer = Application.WorksheetFunction.Min(myRange)
MsgBox answer
品项数量 = WorksheetFunction.CountA(Sheets("订单").range("D10:D16"))
MsgBox 品项数量
储存订单功能
sub 储存订单()
dim 品项数量 as integer, 空白列 as integer
品项数量 = WorksheetFunction.CountA(Sheets("订单").range("D10:D16"))
' 找出空白列位置
空白列 = sheets("资料库").range("a1").CurrentRegion.End(xlDown).offset(1,0).row ' offset 是位移
sheets("订单").range("d10:g10").resize(品项数量).copy ' 调整大小 resize
sheets("资料库").range("d" & 空白列).PasteSpecial xlPasteValues ' & 是文字连接
sheets("资料库").range("a" & 空白列).resize(品项数量).value = sheets("订单").range("g6").value
sheets("资料库").range("b" & 空白列).resize(品项数量).value = sheets("订单").range("g7").value
sheets("资料库").range("c" & 空白列).resize(品项数量).value = sheets("订单").range("d5").value
call 重设表格
msgbox '存储完毕'
End Sub
' 测试 resize, 将所选范围扩大一列一栏
' resize(4,3), 改成四列三栏
Sub testresize()
Worksheets("订单").Activate
numRows = Selection.Rows.Count
numColumns = Selection.Columns.Count
Selection.Resize(numRows + 1, numColumns + 1).Select
End Sub
' 测试 Range.PasteSpecial
Example
.Range("C1:C5").Copy
.Range("D1:D5").PasteSpecial Operation:=xlPasteSpecialOperationAdd
Range("B1").PasteSpecial Paste:=xlPasteValues
Sheets("Sheet1").Range("A1").Copy
Sheets("Sheet2").Range("B1").PasteSpecial Paste:=xlPasteValues
sheets("资料库").range("d" & 空白列).PasteSpecial xlPasteValues
Sub PasteSpecialmethod()
Range("d11").Copy
Range("f12").PasteSpecial xlPasteValues
End Sub
查询功能
sub 查询订单()
dim 订单编号 as integer, 订单位置 as integer, 品项数量 as integer
' 找出订单编号的值, 数一下品项数量,
订单编号 = sheets("订单").range("g6").value
品项数量 = WorksheetFunction.CountIf(Sheets("资料库").range("A:A"), 订单编号)
sheets("订单").range("d5, d10:e16").ClearContents '清空范围
' 假如没查到就说没查到 否则就干活 复制资料到订单明细界面
if 品项数量 = 0 then
msgbox "无此订单"
exit sub
else
订单位置 = WorksheetFunction.Match("订单编号", sheets("资料库").range("A:A"), 0)
sheets("资料库").range("d" & 订单位置).resize(品项数量, 2).copy
sheets("订单").range("d10").PasteSpecial xlPasteValues
sheets("订单").range("g7").value = sheets("资料库").range("b" & 订单位置).value
sheets("订单").range("d5").value = sheets("资料库").range("c" & 订单位置).value
endif
endsub
列印订单
sub 列印订单()
dim 档名 as string
档名 = format(range("g6").value, "00000")
sheets("订单").ExportAsFixedFormat Type:= xlTypePDF Filename:= "D:\我的文档\桌面\excel video\" & 档名, IgnorePrintAreas:=False
endsub
入库登记全功能
Sub 清空()
Range("b4:g4, b8:d8, e8, b12:c12, b24:d24").ClearContents
End Sub
Sub 复制()
Range("临时!b3:g24").ClearContents
Range("入库登记!b3:g24").Copy
Sheets("临时").Range("b3:g24").PasteSpecial Paste:=xlPasteFormulas
End Sub
Sub 粘贴()
Set rng = Range("临时!b3:g24")
rng.Copy
Range("入库登记!b3:g24").PasteSpecial Paste:=xlPasteFormulas
End Sub
Sub 储存()
Dim 空白列 As Integer
空白列 = Sheets("数据库").Range("a2").CurrentRegion.Rows.Count + 1
'类别
Sheets("数据库").Range("a" & 空白列).Value = Sheets("入库登记").Range("b4").Value
Sheets("数据库").Range("b" & 空白列).Value = Sheets("入库登记").Range("c4").Value
Sheets("数据库").Range("c" & 空白列).Value = Sheets("入库登记").Range("d4").Value
Sheets("数据库").Range("d" & 空白列).Value = Sheets("入库登记").Range("e4").Value
Sheets("数据库").Range("e" & 空白列).Value = Sheets("入库登记").Range("f4").Value
Sheets("数据库").Range("f" & 空白列).Value = Sheets("入库登记").Range("g4").Value
'入库
Sheets("数据库").Range("g" & 空白列).Value = Sheets("入库登记").Range("b8").Value
Sheets("数据库").Range("h" & 空白列).Value = Sheets("入库登记").Range("c8").Value
Sheets("数据库").Range("i" & 空白列).Value = Sheets("入库登记").Range("d8").Value
Sheets("数据库").Range("j" & 空白列).Value = Sheets("入库登记").Range("e8").Value
'退货
Sheets("数据库").Range("k" & 空白列).Value = Sheets("入库登记").Range("b12").Value
Sheets("数据库").Range("l" & 空白列).Value = Sheets("入库登记").Range("c12").Value
Sheets("数据库").Range("m" & 空白列).Value = Sheets("入库登记").Range("d12").Value
'实际岀库
Sheets("数据库").Range("n" & 空白列).Value = Sheets("入库登记").Range("b16").Value
Sheets("数据库").Range("o" & 空白列).Value = Sheets("入库登记").Range("c16").Value
'实际库存
Sheets("数据库").Range("p" & 空白列).Value = Sheets("入库登记").Range("b20").Value
Sheets("数据库").Range("q" & 空白列).Value = Sheets("入库登记").Range("c20").Value
'支付信息
Sheets("数据库").Range("r" & 空白列).Value = Sheets("入库登记").Range("b24").Value
Sheets("数据库").Range("s" & 空白列).Value = Sheets("入库登记").Range("c24").Value
Sheets("数据库").Range("t" & 空白列).Value = Sheets("入库登记").Range("d24").Value
Call 复制
Call 清空
MsgBox "存储完毕 第" & 空白列 & "行"
End Sub
Sub 删除列()
Dim 删除列号码, answer As Integer
删除列号码 = InputBox("输入删除列号码")
answer = MsgBox("确认删除第 " & 删除列号码 & " 行", vbQuestion + vbYesNo + vbDefaultButton2, "确认删除!")
If answer = vbYes Then
Range("数据库!" & "a" & 删除列号码).EntireRow.Delete
Else
MsgBox "不删除"
End If
End Sub
action on cell change
Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Address = "$C$12" Or Target.Address = "$F$11" Then 'note the address mustbe capitalise
新入库数量 = Range("$C$12").Value
新退货数量 = Range("$F$11").Value
新实际库存数量 = Range("c20").Value
旧实际库存数量 = Range("c20").Value
差数 = 新实际库存数量 - 旧实际库存数量
ActiveSheet.Unprotect "1" ' unprotext sheet
Range("c12").Value = 新入库数量
Range("f11").Value = 新退货数量
Range("c17") = Range("c17").Value + 差数
ActiveSheet.Protect "1"
End If
End Sub
Dim colNum As Long
colNum = 4
Range("a1").FormulaR1C1 = "=R1C" & colNum & "+R2C" & colNum
vba data validation
Sub data_validation_from_array()
Dim region, product As Variant
Dim region_range, product_range As Range
region = Array("North", "South", "East", "West")
product = Array("TV", "Fridge", "Mobile", "Laptop", "AC")
Set region_range = Range("C5:C10")
Set product_range = Range("D5:D10")
With region_range.Validation
.Delete
.Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop, Formula1:=Join(region, ",")
.IgnoreBlank = True
.InCellDropdown = True
.InputTitle = ""
.ErrorTitle = "Error"
.InputMessage = ""
.ErrorMessage = "Please Provide a Valid Input"
.ShowInput = True
.ShowError = True
End With
End Sub
Use the Validation property of the Range object to return the Validation object.
Range("e5").Validation _
.Modify xlValidateList, xlValidAlertStop, "=$A$1:$A$10"
VBA Examples
Print All Sheet Names
Cells(i, 1).Value = Sheets(i).Name
Insert Different Color Index in VBA
Cells(i, 2).Interior.ColorIndex = i
Insert Worksheets as Much as You want
Worksheets.Add
Delete All Blank Worksheets From the Workbook
If WorksheetFunction.CountA(ws.UsedRange) = 0 Then
ws.Delete
Insert Blank Row
ActiveCell.EntireRow.Insert
Highlight Spelling Mistake
If Not Application.CheckSpelling(Word:=MySelection.Text) Then
MySelection.Interior.Color = vbRed
Change All To Upper Case Characters
Rng.Value = UCase(Rng.Value)
Highlight All the Commented Cells
If Rng.HasFormula = False Then
Rng.Value = LCase(Rng.Value)
Highlight All the Blank Cells
ActiveSheet.UsedRange.SpecialCells(xlCellTypeComments).Interior.ColorIndex = 4
Hide All Sheets Except One Sheet
If Ws.Name <> "Main Sheet" Then Ws.Visible = xlSheetVeryHidden
Unhide All Sheets
For Each Ws In ActiveWorkbook.Worksheets
Ws.Visible = xlSheetVisible
Delete All Files in the Folder
Kill "C:UsersAdmin_2.Dell-PcDesktopDelete Folder*.*"
Delete Entire Folder
Kill "C:UsersAdmin_2.Dell-PcDesktopDelete Folder*.*"
RmDir "C:UsersAdmin_2.Dell-PcDesktopDelete Folder"
Find the Last Used Row in the Sheet
LR = Cells(Rows.Count, 1).End(xlUp).Row
MsgBox LR
Find the Last Used Column in the Sheet
LC = Cells(1, Columns.Count).End(xlToLeft).Column
MsgBox LC
VBA connect to MySQL database in Excel
Sub connect()
Dim Password As String
Dim SQLStr As String
'OMIT Dim Cn statement
Dim Server_Name As String
Dim User_ID As String
Dim Database_Name As String
'OMIT Dim rs statement
Set rs = CreateObject("ADODB.Recordset") 'EBGen-Daily
Server_Name = Range("b2").Value
Database_name = Range("b3").Value ' Name of database
User_ID = Range("b4").Value 'id user or username
Password = Range("b5").Value 'Password
SQLStr = "SELECT * FROM ComputingNotesTable"
Set Cn = CreateObject("ADODB.Connection") 'NEW STATEMENT
Cn.Open "Driver={MySQL ODBC 5.2.2 Driver};Server=" & _
Server_Name & ";Database=" & Database_Name & _
";Uid=" & User_ID & ";Pwd=" & Password & ";"
rs.Open SQLStr, Cn, adOpenStatic
Dim myArray()
myArray = rs.GetRows()
kolumner = UBound(myArray, 1)
rader = UBound(myArray, 2)
For K = 0 To kolumner ' Using For loop data are displayed
Range("a5").Offset(0, K).Value = rs.Fields(K).Name
For R = 0 To rader
Range("A5").Offset(R + 1, K).Value = myArray(K, R)
Next
Next
rs.Close
Set rs = Nothing
Cn.Close
Set Cn = Nothing
End Sub
Check the Drivers tab in the ODBC Data Source
"MySQL ODBC 5.3 Unicode Driver"
Activated Microsoft ActiveX Data Objects 6.1 Library, go Tools/References and check Microsoft Active X Data Objects x library
Enable Microsoft ActiveX Data Objects 2.8 Library
Dim oConn As ADODB.Connection
Private Sub ConnectDB()
Set oConn = New ADODB.Connection
oConn.Open "DRIVER={MySQL ODBC 5.1 Driver};" & _
"SERVER=localhost;" & _
"DATABASE=yourdatabase;" & _
"USER=yourdbusername;" & _
"PASSWORD=yourdbpassword;" & _
"Option=3"
End Sub
instead of looping through every row and column which takes forever. try using
Sub connect()
Dim Password As String
Dim SQLStr As String
'OMIT Dim Cn statement
Dim Server_Name As String
Dim User_ID As String
Dim Database_Name As String
'OMIT Dim rs statement
Set rs = CreateObject("ADODB.Recordset") 'EBGen-Daily
Server_Name = "Server_Name "
Database_Name = "Database_Name" ' Name of database
User_ID = "User_ID" 'id user or username
Password = "Password" 'Password
SQLStr = "SELECT * FROM item"
Set Cn = CreateObject("ADODB.Connection") 'NEW STATEMENT
Cn.Open "Driver={MySQL ODBC 8.0 ANSI Driver};Server=" & _
Server_Name & ";Database=" & Database_Name & _
";Uid=" & User_ID & ";Pwd=" & Password & ";"
rs.Open SQLStr, Cn, adOpenStatic
Range("A2").CopyFromRecordset rs
rs.Close
Set rs = Nothing
Cn.Close
Set Cn = Nothing
End Sub
透視表與列聯表 Contingency table 也稱作交叉列表 cross tabulation 交叉表 crosstab 透視表被認為更為動態，可以在其上執行某些動作； 而列聯表是靜態顯示數據。 Contingency table 列联表 可能性： 用于显示两个或多个分类变量之间关系的表格，通常用于统计分析和概率论。 Contingency 可能发生的事，不测事件；应急措施，应急储备；应急开支；可能性，意外 意外事故 同义词： possibility =COUNTA(D1:D128)
Public Sub MyFilter() Dim dateStart As Long, dateEnd As Long dateStart = Range("E1").Value 'assume this is the start date dateEnd = Range("E2").Value 'assume this is the end date Range("C1:C13").AutoFilter field:=1, Criteria1:=">=" & dateStart, Operator:=xlAnd, Criteria2:="<=" & dateEnd End Sub
Lastly, we get our dataset filtered for the defined criteria range.
Lastly, we can see the result for filtered data in the image below.
In the end, we get the
So, let's see the steps to perform this method.
Let's see the steps which we have to follow in this method.
Lastly, we get a message box displaying the message ‘
We entered all the parameters above for illustrative purposes. In your actual formula, you can use just the value to be searched (“What:="). Instead of using ‘Cells.' At the beginning, you can also specify a particular range. To see how different search options affect the Find method syntax, try recording macros with Excel's built-in Find function (CTRL + F). You can also use .FindNext and .FindPrevious to search for next/pervious matching values. Keep in mind that when you use the Find method once, Excel stores all the parameters you entered (‘SearchFormat', ‘MatchCase', etc.). Thus, if you set ‘MatchCase' to true once, it remains true for subsequent searches as well until you explicitly change it to false. Want to use macros but hate programming? Try this non-coding approach to Excel VBA and macros.
It allows us to filter data based on a Criteria range.
This allows us to do more advanced filtering than the standard filtering on the worksheet.
A second advantage of using Advanced Filter is that we have the option to copy the results to a new range if we choose.
Using Advanced Filter is quite simple as you can see from the dialog:
We filter in-place or we copy to another location.
We then simply need the data range(List range) and the Criteria Range.
If we decided to copy to another location then we provide the "Copy to" range.
Using Advanced Filter is very useful in VBA because it is extremely fast, powerful and as we will see it requires very little code.
The following subsection provides a quick guide to each of the ranges:
This criteria will return the following rows:
We can use multiple rows if we want to filter using OR logic e.g.
If Fruit equals "Apple" OR Fruit equals "Pear" OR Fruit equals "Plum":
Let's have a look at examples of using Multiple Criteria with Advanced Filter:
These criteria return all the rows that have the fruit Orange AND the city Berlin:
In our next example, we are looking for a city that starts with S AND has sales of less than 500:
These criteria will return the following rows:
We can use any column header multiple times in the criteria range.
For example, we can use the Sales column twice to get a number between 300 and 500:
These criteria will return the following rows:
This will return the following rows:
In the following example, we want to return any rows that have a fruit Banana OR sales that are greater than 900:
These criteria will return the following rows:
These are the results:
We want to filter by games where the total number of goals scored was 2. We cannot do this using the normal criteria so we create a formula:
=B5+D5=2
We place this formula in cell A2:
You can see that we have followed the rules above:
Note: If we want to use a formula on another row in the Criteria we should still refer to the first row of the data e.g.:
Cell A2 formula: =B5+D5=2
Cell A3 formula: =B5+D5=5
Cell A3 formula: =B5+D5=7
we get:
If you want to do between dates then you can use the formulas in two columns.
For example, imagine we want to get all the records in August 2021, then we can use the following formulas:
A2: ="<=" & DATE(2021,8,31)
B2: =">=" & DATE(2021,8,1)
This will return the following rows:
If this error occurs you should ensure that the CopyTo range has the correct column headers with the correct spelling and that it is referencing the correct range.
A drop down list of comma separated values works well for small data validation lists that are unlikely to ever change.
For frequently updated lists, you'd better use a range or table for the source.
The detailed step-by-step instructions for each method follow below.
Advantages: Easy and quick way to insert an expandable dynamic drop down menu in Excel.
Drawbacks: Not found :)
How this formula works
The formula comprises two functions - OFFSET and COUNTA.
The COUNTA function counts all non-blanks in the specified column.
OFFSET uses that count for the height argument, so it returns a reference to a range that includes only non-empty cells, starting from the cell containing the first item that you supply for the reference argument.
Advantages: The main advantage of a dynamic drop-down list is that you won't have to change the reference to the named range each time the source list is expanded or contracted.
You simply delete or type new entries in the source list, and your dropdown menu will update automatically!
Drawbacks: A bit complex setup process.
Next, you set up a drop down list using a spill range reference, which is a cell address followed by a hash character.
In our case it's =$E$2# or =Sheet1!$E$2# if a dropdown is in another sheet:
The result is an expandable dynamic drop-down list - the UNIQUE function automatically extracts new items as they are added to the source table, and the spill range reference forces Excel to update the drop-down list accordingly.
And here's an editable Excel dropdown list with a warning message in action:
In the New Name dialog box, in the Name box, type a name for the range.
In the Refers to box, delete the contents, and then keep the cursor in the box.
If you want the name to use a function, enter the function name, and then position the cursor where you want the external reference.
For example, type =SUM(), and then position the cursor between the parentheses.
Switch to the source workbook, and then click the worksheet that contains the cells that you want to link.
Select the cell or range of cells that you want to link, and click OK.
Application: As you can see at the top of the hierarchy there is the Excel application.
Each application can have several workbooks.
Workbooks: Excel files are workbooks.
You can have several excel workbooks (files) open at the same time, but there will only be one Excel application open.
You can test this by looking at the task manager.
Worksheet: Each workbook consists of at least one worksheet.
By default workbooks have 3 worksheets (Sheet1, Sheet2, Sheet3).
The code below will open the file "Data.xlsx", read the first column of data, and close the file:
Sub Example1()
Dim wb As Workbook
Dim i As Integer
'open the workbook with data
Set wb = Workbooks.Open( "D:StuffBusinessTempData.xlsx")
'read the data from the first columns
For i = 1 To 9
Cells(i, 1) = wb.Worksheets("Sheet1").Cells(i + 1, 1)
Next i
'close the workbook
wb.Close
End Sub
Note how the cells in the second workbook were referenced using their complete name:
wb.Worksheets("Sheet1").Cells(i + 1, 1)
We could have also chosen to use this:
wb.Worksheets(1).Cells(i + 1, 1)
Result:
Note: The code below would have resulted in an error:
wb.Sheet1.Cells(i + 1, 1)
Public Number As Integer ' Public Integer variable.
Public NameArray(1 To 5) As String ' Public array variable.
' Multiple declarations, two Variants and one Integer, all Public.
Public MyVar, YourVar, ThisVar As Integer